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 Sell PIVOT ( SUM (Quantity) FOR [ Quarter ] IN ( Q1, Q2, Q3, Q4 ) ) AS P GO --查询得如下结果--注意：--如果子组为空，SQL Server生成空值。如果聚合函数是COUNT，且子组为空，则返回零。 Year Q1 Q2 Q3 Q42006 20 19 12 182007 10 10 NULL NULL2008 8 7 15 9其实PIVOT在sql2000中可以用SELECT...CASE语句来实现，下面是sql2000的代码： -- sql 2000 静态版本 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 ] -- sql 2000 动态版本 DECLARE @sql NVARCHAR ( 2000 ) SELECT @sql = ' select [year] ' SELECT @sql = @sql + ' ,sum(case when [Quarter] = ''' + [ Quarter ] + ''' then Quantity end) as ' + [ Quarter ] FROM sell GROUP BY [ Quarter ] ORDER BY [ Quarter ] ASC select @sql = @sql + ' from sell group by [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 ) p UNPIVOT ( [ Value ] FOR A IN ( A1, A2, A3 ) ) AS u ORDER BY id ASC , a ASC GO --查询得如下结果ID A Value1 A1 q11 A2 q21 A3 q32 A1 q12 A2 p12 A3 m13 A1 t13 A2 p13 A3 m1 -- UNPIVOT 的sql 2000 实现语句： 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运算符的动态版本，这样动态生成列时，不用那么费事的累加字符串