Sql Server2005 Transact-SQL 新兵器学习总结之-PIVOT和UNPIVOT运算符

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  Q4
2006  20   19    12  18
2007  10   10    NULL NULL
2008  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     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 的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运算符的动态版本，这样动态生成列时，不用那么费事的累加字符串

4.推荐
本系列文章(新兵器学习总结)推荐