How to convert a column header and its value into row in sql?

Question

I have a table with columns say col1, col2, col3. The table has many rows in it.

Let's assume val1, val2, val3 is one such row. I want to get the result as

Col1, Val1
Col2, Val2
Col3, Val3

That is 3 rows - one for each column and its value.

I am using SQL Server 2008. I read about pivots. Are pivots a way to solve this problem? Can someone route me to some examples or solutions how to solve this problem?

Thanks a lot

Answer 1

Maybe something like this:

Test data

DECLARE @T TABLE(Col1 INT, Col2 INT, Col3 INT)
INSERT INTO @T
VALUES (1,1,1)

Query

SELECT
    *
FROM
(
    SELECT
        t.Col1,
        t.Col2,
        t.Col3
    FROM
        @T AS t
) AS SourceTable
UNPIVOT
(
    Value FOR Col IN
    (Col1,Col2,Col3)
) AS unpvt

Output

1   Col1
1   Col2
1   Col3

Answer 2

To do this kind of thing read the following: Using PIVOT and UNPIVOT

Pivot function allow you to convert row values in from of column..

Also check : Dynamic Pivoting in SQL Server

Example :

create table #temptable(colorname varchar(25),Hexa varchar(7),rgb varchar(1), rgbvalue tinyint)    
GO   
insert into #temptable values('Violet','#8B00FF','r',139);   
insert into #temptable values('Violet','#8B00FF','g',0);   
insert into #temptable values('Violet','#8B00FF','b',255);   
insert into #temptable values('Indigo','#4B0082','r',75);   
insert into #temptable values('Indigo','#4B0082','g',0);   
insert into #temptable values('Indigo','#4B0082','b',130);   
insert into #temptable values('Blue','#0000FF','r',0);   
insert into #temptable values('Blue','#0000FF','g',0);   
insert into #temptable values('Blue','#0000FF','b',255);



SELECT colorname,hexa,[r], [g], [b]   
FROM  
(SELECT colorname,hexa,rgb,rgbvalue   
    FROM #temptable) AS TableToBePivoted   
PIVOT   
(   
sum(rgbvalue)   
FOR rgb IN ([r], [g], [b])   
) AS PivotedTable;