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

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

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:

select [<<unchanged columns>>],<<new column>>,<<new column for its values>>
from 
(
	select [<<unchanged columns>>],<<columns to be converted to rows>>	from <<tablename>>
) <<alias name1>>
UNPIVOT 
(
	<<new column for its values>> FOR 
	<<new column>> IN (<<columns to be converted to rows>>)
)
AS <<alias name2>>

Thats it, hope it helps.