Query Data and Column Names

[Shisou]

Hello all,

I'm trying to create some reports and I've run into a bit of a tricky query... I need 1 query that will basically return 2 columns...

column1 will be a list of certain column names in the table... like columnA, columnB, columnC etc....

column2 will be a list containing the value of the corresponding column name in column1... so the end result will look like

column1       column2
--------     ---------
columnA      valueA
columnB      valueB
columnC      valueC 

and so on....

Also, if someone can give me a solution to this I would also like to know if all the values would have to be of the same datatype.

Thanks in advance!!

[ck9663]

You're going to need to build a dynamic query. You can get the column names from sys.columns or syscolumns and link that to sys.tables or sysobjects.

Happy Coding!!!

--- CK

[nbiswas]

Lets say I have a table MyTable which has 2 columns Attribute and Value both of type varchar.

I will execute the following query

;with cte1 as
(
select 
    ROW_NUMBER() over(order by ordinal_position) as rn,  
    column_name 
from information_schema.columns
where table_name = 'MyTable'
)
,cte2 as
(
select    attribute, 1 as rn1,
        value, 2 as rn2
from MyTable
)
select    c1.column_name as column1
        ,c2.attribute as column2 
from cte1 c1
inner join cte2 c2
on c1.rn = c2.rn1
 
union all
 
select    c1.column_name as column1
        ,c2.value as column2 
from cte1 c1
inner join cte2 c2
on c1.rn = c2.rn2

This query is for SQL SERVER 2005+ .
In the first CTE I am picking up the column names and ensuring their orders via the row_number() function.
In the next part I am picking up the information from the MyTable table and explicity assigning 1 to the 1st column values(i.e. Attribute) here and 2 for the Value(i.e. 2nd column)

Then by using innerjoin followed by union I have accomplishing the task.

Note:- This is only an example and one of the many approaches. Take the idea if you have not get one as of now and put it as per your need.

Hope this helps.