Transpose Rows to Columns in Sql Server 2005 using PIVOT

As known SQL Server 2000 need some dynamic sqls to convert rows to columns or vice versa. PIVOT and UNPIVOT relational operator did the job of converting rows to columns and columns to rows respectively.

Below is the sample that transpose values from rows to columns in sql server 2005 using PIVOT relational operator,

Sample input table

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);
GO
select * from #temptable
GO

The sample contains colors (Violet, Indigo, Blue) with its specific "R","G" & "B" values in each row. Our goal is to convert "R", "G" & "B" as column with specific value for each colors. This can be done using PIVOT. PIVOT uses a aggregate function to compile rows to columns so we not require to specify group by explicitly.

The T-SQL query to convert rows to columns is

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;

The output would be

In the above "SUM" aggregate function is used which would sum rgbvalue for same colorname,hexa mentioned in select statement. "rgb" column is tranfered to 3 columns namely "r, "g" & "b" using its value. syntax for understanding:

SELECT [<<unchanged columns>>],<<new columns for pivot>>
FROM
(SELECT [<<unchanged columns>>],<<source columns to be pivoted>>
    FROM <<tablename>>) AS <<alias name1>>
PIVOT
(
<<aggregate function>>(<<column with has values>>)
FOR <<source column to be pivoted>> IN (<<new columns for pivot>>)
) AS <<alias name2>>;

Thats it, hope it helps.