how to select columns as rows?

Question

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!

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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.