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.
