Solusi dan Pemrograman Transact-SQL

UNPIVOT Problem and Solution

“Applicable to SQL Server 2005 and above”

PIVOT and UNPIVOT are T-SQL feature that have been introduced  since SQL Server 2005. PIVOT a row means rotating data from state of rows into state of columns like the example below :

Original data :

Pivot result :

What could you see from above illustration? Jakarta, Bandung, Denpasar becomes names columns, whilst its freight_cost becomes values columns. This is very important concept to grasp; NAMES COLUMNS and VALUES COLUMNS.

UNPIVOT will do reverse than PIVOT has done earlier. Both PIVOT and UNPIVOT must contain 2 type of columns as mentioned above.

I discuss about UNPIVOT operator in this posting. Syntax of UNPIVOT is

UNPIVOT received input from table/table expression before it as an input and it returns a table resultset as an output. UNPIVOT is regarded as TABLE operator like JOIN operator is. Because it is TABLE operator, it must have a table alias. Resultset of UNPIVOT could became input for next TABLE operator or further processed by condition after WHERE clause.

To demonstrate use of UNPIVOT, here is script for table creation with sample rows, here :

create table dbo.PivotTable(     shipperid int,     Jakarta int,     Bandung int,     Denpasar int);
go
insert into dbo.PivotTable values (1,20000,10000,50000);
insert into dbo.PivotTable values (2,40000,50000,60000);
insert into dbo.PivotTable values (3,100000,200000,300000);

UNPIVOT statement for the dbo.PivotTable is :

select D.shipperid, D.shipcity, D.freight_costfrom dbo.PivotTable as P
UNPIVOT (freight_cost for shipcity in(Jakarta,Bandung,Denpasar)) as D

Result of UNPIVOT is :

shipperid	shippercity	freight_cost
1	Jakarta	20000
1	Bandung	10000
1	Denpasar	50000
2	Jakarta	40000
2	Bandung	50000
2	Denpasar	60000
3	Jakarta	100000
3	Bandung	200000
3	Denpasar	300000

 

This is part of Transact SQL series that give broad opportunity to turn data into useful information. Many things you could solve business challenge with strong knowledge in transact-SQL.

Share this post: | | | |

Published Tuesday, February 1, 2011 6:20 PM by Kasim.Wirama

Filed under: Rubrik Solusi dan Teknik Pemrograman Transact SQL