In SQL Server 2005, a new feature has been included to facilitate conversions of row-to-column and column-to-row.
Before SQL Server 2005, it will spend us much efforts to write such a T-SQL statement. Now, everything changed since
PIVOT and UNPIVOT have been introduced.
Let us see two typical conversion scenarios which can be resolved by PIVOT and UNPIVOT now in SQL 2005.
Column-to-Row Conversion
Take the sample database Adventureworks for example. I would like to query the non-flagged and flagged vacation
hours in average. Generally we can get the result by executing:
SELECT SalariedFlag, AVG(VacationHours) AS AVG_VHours FROM HumanResources.Employee GROUP BY SalariedFlag
The output is something like the following:
SalariedFlag AVG_VHours
----------- ------------
0 12
1 30
However if I would like to get the result as something like:
NON_FLAGGED(0) FLAGGED(1)
------------------------- ----------------
12 30
it is very convenient to get this by executing the following statement:
SELECT [0] AS NON_FLAGGED, [1] as FLAGGED
FROM
(
SELECT SalariedFlag, VacationHours
FROM HumanResources.Employee
) AS H
PIVOT
(
AVG(VacationHours)
FOR SalariedFlag IN ([0], [1])
) AS Pvt
Row-to-Column Conversion
Think about the following scenario, if there is a table like this:
create table SALES
(
SALESID int identity(1,1) primary key,
SH int,
BJ int,
XM int,
NJ int,
COUNTDATE datetime
)
INSERT INTO SALES VALUES(3000,4000,5000,6000,'2007-05-01')
INSERT INTO SALES VALUES(4000,4000,8000,7000,'2007-06-01')
INSERT INTO SALES VALUES(9000,10000,7000,7000,'2007-10-01')
I would like to retrieve the results like this:
2007-05-01 SH 3000
2007-05-01 BJ 4000
2007-05-01 XM 5000
2007-05-01 NJ 6000
2007-06-01 SH 4000
......
Now we can use UNPIVOT to get the result as we expected:
SELECT COUNTDATE, LOCATION, SALEVALUE
FROM
(
SELECT SH,BJ,XM,NJ,COUNTDATE FROM SALES
) V1
UNPIVOT
(SALEVALUE FOR LOCATION IN (SH,BJ,XM,NJ)) AS V2
ORDER BY COUNTDATE,LOCATION
