Transpose Columns to Rows in Sql Server 2005 using UNPIVOT

In Sql Server 2000, it was very hard converting column based values to transpose into row based values and vice versa.


PIVOT and UNPIVOT did the trick in Sql server 2005.



Below is the sample that transpose values from column to rows in sql server using UNPIVOT relational operator.



Sample input table





view plaincopy to clipboardprint?

01.create table #temptable(rowid int,colorname varchar(25),Hexa varchar(7)

02.,R tinyint,G tinyint,B tinyint)

03.GO

04.insert into #temptable values(1,'Violet','#8B00FF',139,0,255);

05.insert into #temptable values(2,'Indigo','#4B0082',75,0,130);

06.insert into #temptable values(3,'Blue','#0000FF',0,0,255);

07.insert into #temptable values(4,'Green','#00FF00',0,255,0);

08.insert into #temptable values(5,'Yellow','#FFFF00',255,255,0);

09.insert into #temptable values(6,'Orange','#FFA500',255,165,0);

10.insert into #temptable values(7,'Red','#FF0000',255,0,0);

11.GO

12.select * from #temptable

13.GO

create table #temptable(rowid int,colorname varchar(25),Hexa varchar(7)

,R tinyint,G tinyint,B tinyint)

GO

insert into #temptable values(1,'Violet','#8B00FF',139,0,255);

insert into #temptable values(2,'Indigo','#4B0082',75,0,130);

insert into #temptable values(3,'Blue','#0000FF',0,0,255);

insert into #temptable values(4,'Green','#00FF00',0,255,0);

insert into #temptable values(5,'Yellow','#FFFF00',255,255,0);

insert into #temptable values(6,'Orange','#FFA500',255,165,0);

insert into #temptable values(7,'Red','#FF0000',255,0,0);

GO

select * from #temptable

GO



The sample table contains colorname with "R","G" & "B" column with specific values.



Our goal is to convert "R","G" & "B" as rows with specific values in single column.



Main requirement of transposing columns to rows is to have identical datatypes of converting columns.

In the sample the datatype of "R", "G" & "B" being tinyint.





The T-SQL query to convert columns to rows is




01.SELECT rowid,colorname,hexa,rgb,rgbvalue

02. FROM

03. (SELECT rowid,colorname,hexa,r,g,b

04. FROM #temptable) p

05. UNPIVOT

06. (rgbvalue FOR rgb IN (r,g,b))

07. AS unpvt;

SELECT rowid,colorname,hexa,rgb,rgbvalue

FROM

(SELECT rowid,colorname,hexa,r,g,b

FROM #temptable) p

UNPIVOT

(rgbvalue FOR rgb IN (r,g,b))

AS unpvt;

The output would be







In the above sample "rowid,colorname,hexa" columns remains unchanged. "R", "G" & "B" columns are converted to rows with new column "rgb" and its specific values are added to new column "rgbvalue".



syntax for understanding:




01.select [<>],<>,<>

02.from

03.(

04. select [<>],<> from <>

05.) <>

06.UNPIVOT

07.(

08. <> FOR

09. <> IN (<>)

10.)

11.AS <>