how to select columns as rows?

Asked 16/10, 2009 at 14:43 Answered 18/1, 2023 at 15:32

So, I've been searching around and I've found things similar to my problem, but I need more help to get a real solution.

I'm trying to construct a query that will return 2 columns of data, the first column should be a list of the column names themselves and the second should be the value of that column.

Visually it would look like this

Column1      Column2
-------      -------
columnA      value_of_columnA
columnB      value_of_columnB
...          ...

I'm pretty sure that this is going to require dynamic SQL to achieve, but I have no idea how to even begin creating the query.

Any help is appreciated!

Pinnati answered 16/10, 2009 at 14:43 Comment(8)

Your question lacks any real details - what table(s) are you dealing with, and what are the column datatypes? Version of SQL Server too, pls. – Timm 16/10, 2009 at 14:49

what version of SQL Server specifically? And are you counting on only having 1 row of data, or will Column 1 be the column name and all other columns the data for that column? – Monroy 16/10, 2009 at 14:50

I can understand wanting to pivot rows into columns, but columns into rows? What are you actually trying to do? – Helices 16/10, 2009 at 14:52

Sorry for the lack of detail, the query is running against a mssql 2000 db. Column 1 is only going to be column_names... like what this query would pull SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'table' and column2 would be the information pulled from this query SELECT column1, column2, column3... FROM table WHERE blah = 'blah' Except I need that query to be dynamically pulled depending on what the first query pulls, where column1, column2, column3 would be filled in with the results of the first query. Also, all the data in the second column should be boolean – Pinnati 16/10, 2009 at 14:59

I have to ask, why on earth would you want to do this? Unless your table has only one record it makes no sense. – Buzzer 16/10, 2009 at 15:2

The reason I'm doing this is because I need to fill a report with the column names and then give the data for that column name. I don't quite see how this is only useful for 1 record. – Pinnati 16/10, 2009 at 21:12

I have updated my answer with a SQL Server 2000 solution – Rojas 16/10, 2009 at 21:43

His question is essentially asking how to break relational theory. – Fresno 16/10, 2009 at 22:36

This should work for any table, but in my example I just create a test one. You need to set the table name within @YourTableName. Also, you need to set @YourTableWhere to limit the results to one row, otherwise the output looks strange with multiple rows mixed together.

try this:

BEGIN TRY
CREATE TABLE YourTestTable
(RowID       int primary key not null identity(1,1)
,col1        int null
,col2        varchar(30)
,col3        varchar(20)
,col4        money
,StatusValue char(1)
,xyz_123     int
)
INSERT INTO YourTestTable (col1,col2,col3,col4,StatusValue,xyz_123) VALUES (1234,'wow wee!','this is a long test!',1234.56,'A',98765)
INSERT INTO YourTestTable (col1,col2,col3,col4,StatusValue,xyz_123) VALUES (543,'oh no!','short test',0,'I',12)

END TRY BEGIN CATCH END CATCH

select * from YourTestTable


DECLARE @YourTableName   varchar(1000)
DECLARE @YourTableWhere  varchar(1000)
DECLARE @YourQuery       varchar(max)

SET @YourTableName='YourTestTable'
set @YourTableWhere='y.RowID=1'

SELECT
    @YourQuery = STUFF(
                       (SELECT
                            ' UNION '
                            + 'SELECT '''+COLUMN_NAME+''', CONVERT(varchar(max),'+COLUMN_NAME+') FROM '+@YourTableName+' y'+ISNULL('  WHERE '+@YourTableWhere,'')
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE table_name = @YourTableName
                            FOR XML PATH('')
                       ), 1, 7, ''
                      )

PRINT @YourQuery  

EXEC (@YourQuery)

OUTPUT:

RowID       col1        col2                           col3                 col4                  StatusValue xyz_123
----------- ----------- ------------------------------ -------------------- --------------------- ----------- -----------
1           1234        wow wee!                       this is a long test! 1234.56               A           98765
2           543         oh no!                         short test           0.00                  I           12

SELECT 'RowID', CONVERT(varchar(max),RowID) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'col1', CONVERT(varchar(max),col1) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'col2', CONVERT(varchar(max),col2) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'col3', CONVERT(varchar(max),col3) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'col4', CONVERT(varchar(max),col4) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'StatusValue', CONVERT(varchar(max),StatusValue) FROM YourTestTable y  WHERE y.RowID=1 UNION SELECT 'xyz_123', CONVERT(varchar(max),xyz_123) FROM YourTestTable y  WHERE y.RowID=1

----------- ------------------------
col1        1234
col2        wow wee!
col3        this is a long test!
col4        1234.56
RowID       1
StatusValue A
xyz_123     98765

EDIT

For SQL Server 2000 compatibility, you should be able to replace varchar(max) with varchar(8000) and use this in place of the SELECT @YourQuery query from the code above:

SELECT
    @YourQuery=ISNULL(@YourQuery+' UNION ','')
        + 'SELECT '''+COLUMN_NAME+''', CONVERT(varchar(max),'+COLUMN_NAME+') FROM '+@YourTableName+' y'+ISNULL('  WHERE '+@YourTableWhere,'')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = @YourTableName

Rojas answered 16/10, 2009 at 18:40 Comment(1)

This looks like exactly what I need... except... I'm using MSSQL 2000 database, so varchar(max) doesn't work *though I got around that with varchar(1000) and FOR XML PATH('') also does not work. I'm not sure how to get around that one... – Pinnati 16/10, 2009 at 21:19

select column_name,* from information_schema.columns
 where table_name = 'TheTableName'
order by ordinal_position

Hygrometry answered 16/10, 2009 at 14:49 Comment(0)

You could always do something like this

SELECT 'Column_Name' AS ColumnName, 
  (SELECT TOP 1 Column_Name FROM Table tbl2 WHERE tbl.ID = tbl2.ID)
FROM Table tbl

Agnella answered 16/10, 2009 at 14:49 Comment(0)

My answer to this question will work more easily with SQL Server 2000 because it doesn't use the XML features of SQL Server 2005.

Glutton answered 16/10, 2009 at 21:56 Comment(0)

You aren't very clear about how you are presenting your report and what you are generating it with. Are you using direct results from the query tool to generate your "report"? In which case, methinks you are trying to pound a nail using a screwdriver. Use the right tool for the job.

The SQL language, directly, shouldn't be used to setup your presentation data to generate your report. Really, it's a silly idea. The fact that you can write a report with straight-up SQL statements doesn't mean that you should.

You really ought to generate your report using an application program that you write yourself, or a report generation tool like Crystal Reports.

Application Program written by yourself: If you are using a cursor object to query the database, you can simply get the column names from that cursor object. Problem solved.

Report Generation Tool: usually they provide a facility to represent the dynamic data that could appear.

Either way, I think you need to rethink your approach to this.

Fresno answered 16/10, 2009 at 22:12 Comment(0)

This should work from SQL 2008

select Col_Name, Col_Value FROM YOUR_TABLE UNPIVOT (Col_Value FOR Col_Name IN ( columnA, columnB, columnC, columnD) ) AS UnpivotTable

Michaelson answered 18/1, 2023 at 15:32 Comment(0)

Recommended topics

Hot tags