数据库行列转换—PIVOT和UNPIVOT运算符的技巧

原文链接：http://www.cnblogs.com/aierong/archive/2008/09/01/1279463.html

1.简介
PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。
UNPIVOT与PIVOT执行相反的操作，将表值表达式的列转换为列值。
通俗简单的说：PIVOT就是行转列，UNPIVOT就是列传行

2.例题

-- 建立销售表
CREATE TABLE Sell
(
[ Year ] INT ,
[ Quarter ] NVARCHAR ( 10 ),
Quantity INT
)

-- 插入测试数据
INSERT INTO Sell
VALUES ( 2006 , ' Q1 ' , 20 )
INSERT INTO Sell
VALUES ( 2006 , ' Q2 ' , 15 )
INSERT INTO Sell
VALUES ( 2006 , ' Q2 ' , 4 )
INSERT INTO Sell
VALUES ( 2006 , ' Q3 ' , 12 )
INSERT INTO Sell
VALUES ( 2006 , ' Q4 ' , 18 )

INSERT INTO Sell
VALUES ( 2007 , ' Q1 ' , 10 )
INSERT INTO Sell
VALUES ( 2007 , ' Q2 ' , 10 )

INSERT INTO Sell
VALUES ( 2008 , ' Q1 ' , 8 )
INSERT INTO Sell
VALUES ( 2008 , ' Q2 ' , 7 )
INSERT INTO Sell
VALUES ( 2008 , ' Q3 ' , 5 )
INSERT INTO Sell
VALUES ( 2008 , ' Q3 ' , 10 )
INSERT INTO Sell
VALUES ( 2008 , ' Q4 ' , 9 )
GO

-- 得到每年每季度的销售总数
SELECT *
FROM SellPIVOT( SUM (Quantity) FOR [ Quarter ] IN (Q1,Q2,Q3,Q4)) AS P
GO

--查询得如下结果
--注意：
--如果子组为空，SQL Server生成空值。如果聚合函数是COUNT，且子组为空，则返回零。
Year Q1 Q2 Q3 Q4
2006 20 19 12 18
2007 10 10 NULL NULL
20088 7 15 9

其实PIVOT在sql2000中可以用SELECT...CASE语句来实现，下面是sql2000的代码：

-- sql2000静态版本
SELECT [ year ] ,
SUM ( CASE WHEN [ Quarter ] = ' Q1 ' THEN Quantity
END ) AS Q1,
SUM ( CASE WHEN [ Quarter ] = ' Q2 ' THEN Quantity
END ) AS Q2,
SUM ( CASE WHEN [ Quarter ] = ' Q3 ' THEN Quantity
END ) AS Q3,
SUM ( CASE WHEN [ Quarter ] = ' Q4 ' THEN Quantity
END ) AS Q4
FROM sell
GROUP BY [ year ]

-- sql2000动态版本
DECLARE @sql NVARCHAR ( 2000 )
SELECT @sql = ' select[year] '

SELECT @sql = @sql + ' ,sum(casewhen[Quarter]= ''' + [ Quarter ]
+ ''' thenQuantityend)as ' + [ Quarter ]
FROM sell
GROUP BY [ Quarter ]
ORDER BY [ Quarter ] ASC

select @sql = @sql + ' fromsellgroupby[year] '

execute ( @sql )

UNPIVOT将与PIVOT执行几乎完全相反的操作，将列转换为行。

-- 创建测试表
CREATE TABLE TestUNPIVOT
(
ID INT ,
A1 NVARCHAR ( 10 ),
A2 NVARCHAR ( 10 ),
A3 NVARCHAR ( 10 )
)

-- 插入测试数据
INSERT INTO TestUNPIVOT
VALUES ( 1 , ' q1 ' , ' q2 ' , ' q3 ' )
INSERT INTO TestUNPIVOT
VALUES ( 2 , ' q1 ' , ' p1 ' , ' m1 ' )
INSERT INTO TestUNPIVOT
VALUES ( 3 , ' t1 ' , ' p1 ' , ' m1 ' )
GO

-- UNPIVOT
SELECT ID,
A,
[ Value ]
FROM ( SELECT ID,
A1,
A2,
A3
FROM TestUNPIVOT
)pUNPIVOT( [ Value ] FOR A IN (A1,A2,A3)) AS u
ORDER BY id ASC ,
a ASC
GO

--查询得如下结果
ID A Value
1 A1 q1
1 A2 q2
1 A3 q3
2 A1 q1
2 A2 p1
2 A3 m1
3 A1 t1
3 A2 p1
3 A3 m1

-- UNPIVOT的sql2000实现语句：
SELECT id,
' a1 ' AS [ A ] ,
a1 AS [ Value ]
FROM TestUNPIVOT
UNION ALL
SELECT id,
' a2 ' ,
A2
FROM TestUNPIVOT
UNION ALL
SELECT id,
' a3 ' ,
A3
FROM TestUNPIVOT
ORDER BY id ASC ,a ASC

3.总结
个人感觉PIVOT运算符相比SELECT...CASE语句就是代码精简了一些,似乎PIVOT可读性好像不太好！
至少我看起来PIVOT语法有点怪怪，也许是还习惯吧！我个人还是喜欢用SELECT...CASE语句.
希望微软能提供PIVOT运算符的动态版本，这样动态生成列时，不用那么费事的累加字符串