[学习SQL SERVER 2005系列]感受新功能一：PIVOT

来源：互联网发布：final cut pro 软件编辑：程序博客网时间：2024/05/16 09:13
[学习SQL SERVER 2005系列]感受新功能一：PIVOT
    工具的升级，我以为得先看看这个工具在哪些功能上得到加强，今天我们就看看SQL2005这个PIVOT吧。PIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。记得我们在SQL2000中要用聚合和CASE语句完成一个行列转换吧，特别当待转成列的数据不定时，我们往往构造动态SQL，然后用EXEC来运行。
    环境准备：
------------------------------------
-- Author:  happyflsytone  
-- Version:V1.001  
-- Date:2008-09-18 10:20:53
------------------------------------
-- Test Data: ta
IF OBJECT_ID('ta') IS NOT NULL 
    DROP TABLE ta
;
CREATE  TABLE ta(id INT,col1 Nvarchar(2),col2 Nvarchar(2),col3 Nvarchar(4),col4 INT)
;
INSERT INTO ta
SELECT 1,'HN','CS','abc',1 UNION ALL
SELECT 2,'HN','CS','abcd',2 UNION ALL
SELECT 3,'HN','CD','abcd' ,3UNION ALL
SELECT 4,'HN','HY','ae' ,4
;
    我们先来回顾SQL2000的行列转换,比如我们对上例程把col3转列显示，并把col4的和当对应列值。我们分两种情况来讨论：
    一、当col3的列值固定就是'abc','abcd','ae'三种情况
SELECT 
    col1,
    col2,
    [abc]   = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),
    [abcd]  = SUM(CASE WHEN col3 = 'abcd' THEN col4  ELSE 0 END),
    [ae]    = SUM(CASE WHEN col3 = 'ae' THEN col4  ELSE 0 END)
FROM ta
GROUP BY col1,col2
/*
col1 col2 abc         abcd        ae
---- ---- ----------- ----------- -----------
HN   CD   0           3           0
HN   CS   1           2           0
HN   HY   0           0           4
(3 行受影响)
*/
    二、当col3的列值不固定时就运用动态SQL，其实也就是构造一个sum(CASE WHEN ...)SQL字符串
DECLARE @s varchar(8000)
SELECT @s = isnull(@s+',
    ','') +'['+col3+'] = SUM(CASE WHEN col3 = '''+col3+''' THEN col4 ELSE 0 END)'
FROM ( SELECT distinct col3 FROM ta) a
SET @s = 'SELECT 
    col1,
    col2,
    '+@s + ' 
FROM ta 
GROUP BY 
    col1,col2'
EXEC(@s)
/*
col1 col2 abc         abcd        ae
---- ---- ----------- ----------- -----------
HN   CD   0           3           0
HN   CS   1           2           0
HN   HY   0           0           4
(3 行受影响)
*/
    我们先输入这个@S看看是什么东东，只要加上print @s
    
SELECT 
    col1,
    col2,
    [abc] = SUM(CASE WHEN col3 = 'abc' THEN col4 ELSE 0 END),
    [abcd] = SUM(CASE WHEN col3 = 'abcd' THEN col4 ELSE 0 END),
    [ae] = SUM(CASE WHEN col3 = 'ae' THEN col4 ELSE 0 END) 
FROM ta 
GROUP BY 
    col1,col2
    
    其实就是上面我们构造的固定列值的SQL嘛。
    好，现在们开始在2005中实现这个功能，先来看看2005的FROM子句的定义(关于如何看这个定义请参照SQL2005的文档约定及Transate-SQL语法约定)：
[ FROM { <table_source> } [ ,...n ] ] 
<table_source> ::= 
{
     <pivoted_table> 
}
<pivoted_table> ::=
        table_source PIVOT <pivot_clause> table_alias
<pivot_clause> ::=
        ( aggregate_function ( value_column ) 
        FOR pivot_column 
        IN ( <column_list> ) 
    ) 
<column_list> ::=
          column_name [ , ... ]
pivot_column 和 value_column 是 PIVOT 运算符使用的组合列。PIVOT 遵循以下过程获得输出结果集：
对分组列的 input_table 执行 GROUP BY，为每个组生成一个输出行。
输出行中的分组列获得 input_table 中该组的对应列值。
通过执行以下操作，为每个输出行生成列列表中的列的值：
针对 pivot_column，对上一步在 GROUP BY 中生成的行另外进行分组。
对于 column_list 中的每个输出列，选择满足以下条件的子组：
pivot_column = CONVERT(<data type of pivot_column>, 'output_column')
针对此子组上的 aggregate_function 对 value_column 求值，其结果作为相应的 output_column 的值返回。如果该子组为空，SQL Server 将为该 output_column 生成空值。如果聚合函数是 COUNT，且子组为空，则返回零 (0)。 
    接着我们利用我们开头的例子来理解一下这个FROM子句，很显然我们的col4对应上面的value_column,我们还假定列会下固定为这三项，那么列 col3 对应上面的pivot_column,进而我们应该得出[abc],[abcd],[ae]是column_name即我们的输出列，最后我们只要构造一下table_source就可以了，如何构造这个table_source，显然pivot_column 和 value_column应该包含在其中，其它就应该是你想要分组的列啦.
    我们来总结一下：这个FROM子句是基于 table_source 对 pivot_column 进行透视，table_source 中 pivot_column 和 value_column 列之外的列被称为透视运算符的组合列,而PIVOT 是对输入表执行组合列的分组操作，并为每个组返回一行，好，我们试着写出这个SQL：
    
SELECT col1,col2,[abc],[abcd],[ae]
FROM 
   (SELECT col1,col2,col3,col4
    FROM ta ) p
PIVOT
   ( SUM (col4) 
    FOR col3 IN ([abc],[abcd],[ae])
   )AS unpvt
   
我们执行一下看看结果：
/*
col1 abc         abcd        ae
---- ----------- ----------- -----------
HN   1           NULL        NULL
HN   NULL        2           NULL
HN   NULL        3           NULL
HN   NULL        NULL        4
(4 行受影响)
*/
如果我们去掉这些NULL那么可以这样：
SELECT col1,col2,ISNULL([abc],0) AS [ABC],ISNULL([abcd],0) AS [ABCD],ISNULL([ae],0) AS [AE]
FROM 
   (SELECT col1,col2,col3,col4
    FROM ta ) p
PIVOT
   ( SUM (col4) 
    FOR col3 IN ([abc],[abcd],[ae])
   )AS unpvt
/*
col1 col2 ABC         ABCD        AE
---- ---- ----------- ----------- -----------
HN   CD   0           3           0
HN   CS   1           2           0
HN   HY   0           0           4
(3 行受影响)
*/
当然在2005中列值不固定时也要用到动态SQL，我们把这个例子完成如下：
DECLARE @s VARCHAR(1000)
SELECT @s = isnull(@s + ',','')+ '['+ltrim(COL3)+']'
FROM (SELECT DISTINCT col3 FROM ta ) a
EXEC('SELECT col1,col2,'+@s+'
FROM 
   (SELECT col1,col2,COL3,COL4
   FROM TA) p
PIVOT
   ( SUM (COL4) 
    FOR COL3 IN ('+@s+')
   )AS unpvt')
/*
col1 col2 abc         abcd        ae
---- ---- ----------- ----------- -----------
HN   CD   NULL        3           NULL
HN   CS   1           2           NULL
HN   HY   NULL        NULL        4
(3 行受影响)
*/
最后我们再完成一个table_source是多表关联的例子，准备数据如下：
-- Test Data: ta
If object_id('ta') is not null 
    Drop table ta
;
Create table ta(id int,省 nvarchar(2),市 nvarchar(2),具体货品 nvarchar(4))
;
Insert into ta
select 1,'HN','CS','abc' union all
select 2,'HN','CS','abcd' union all
select 3,'HN','CD','abcd' union all
select 4,'HN','HY','ae' 
;
-- Test Data: tb
If object_id('tb') is not null 
    Drop table tb
;
Create table tb(编号 int,具体货品 nvarchar(5),大类别 int)
;
Insert into tb
select 1,'abc',1 union all
select 2,'abcd',2 union all
select 3,'abcde',1 union all
select 4,'ae',3 
Go
--Start
-----2005写法
select @s = isnull(@s + ',','')+ '['+ltrim(大类别)+']'
from (select distinct top 100 percent  大类别 from tb order by 大类别 ) a
exec('SELECT 省,市,'+@s+'
FROM 
   (SELECT 省,市,大类别,a.编号
   FROM ta a left join tb b on a.具体货品 = b.具体货品) p
PIVOT
   ( COUNT (编号) 
    FOR 大类别 IN ('+@s+')
   )AS unpvt')
--Result:
/*
(3 行受影响)
省    市    1           2           3
---- ---- ----------- ----------- -----------
HN   CD   0           1           0
HN   CS   1           1           0
HN   HY   0           0           1
(3 行受影响)
*/
--End 
好，我们对2005的PIVOT这个新功能的学习就到这儿了，多练习就可以熟练的使用这个PIVOT用法。