How to select columns as rows?

Question

So, I searched and found things similar to my problem, but I need additional help to get a real solution.

I am trying to build 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 this column.

Visually it will look like

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

I am sure that this will require dynamic SQL, but I do not know how to start creating a query.

Any help is appreciated!

Answer 1

This should work for any table, but in my example I just create a test one. You need to set the table name to @YourTableName. In addition, you need to set @YourTableWhere to limit the results to one line, otherwise the output looks strange if several lines are mixed together.

try the following:

 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 compatibility with SQL Server 2000, you can replace varchar (max) with varchar (8000) and use this instead of SELECT @YourQuery from the above code:

 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 

Answer 2

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

Answer 3

You can 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 

Answer 4

My answer to this question will be easier to work with SQL Server 2000 as it does not use the SQL Server 2005 XML features.

Answer 5

You do not quite understand how you present your report and what you generate it. Do you use direct results from the query tool to create your "report"? In this case, you may be trying to fix the nail with a screwdriver. Use the right tool for the job.

The SQL language, directly, should not be used to configure your presentation data to create a report. Indeed, this is a stupid idea. The fact that you can write a report with direct SQL statements does not mean you should.

You really have to generate a report using the application you are writing or a reporting tool like Crystal Reports.

An application written by you yourself: if you use a cursor object to query a database, you can simply get the column names from that cursor object. The problem is resolved.

Reporting tool: they usually provide the ability to present dynamic data that can be displayed.

In any case, I think you need to reconsider your approach to this.