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

Acfe

• [学习SQLSERVER2005系列]感受新功能一：PIVOT
  
• 
  
• 工具的升级，我以为得先看看这个工具在哪些功能上得到加强，今天我们就看看SQL2005这个PIVOT吧。PIVOT关系运算符对表值表达式进行操作以获得另一个表。PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。记得我们在SQL2000中要用聚合和CASE语句完成一个行列转换吧，特别当待转成列的数据不定时，我们往往构造动态SQL，然后用EXEC来运行。
  
• 环境准备：
  
• ------------------------------------
  
• --Author:happyflsytone
  
• --Version:V1.001
  
• --Date:2008-09-1810:20:53
  
• ------------------------------------
  
• 
  
• --TestData:ta
  
• IFOBJECT_ID('ta')ISNOTNULL
  
• DROPTABLEta
  
• ;
  
• CREATETABLEta(idINT,col1Nvarchar(2),col2Nvarchar(2),col3Nvarchar(4),col4INT)
  
• ;
  
• INSERTINTOta
  
• SELECT1,'HN','CS','abc',1UNIONALL
  
• SELECT2,'HN','CS','abcd',2UNIONALL
  
• SELECT3,'HN','CD','abcd',3UNIONALL
  
• SELECT4,'HN','HY','ae',4
  
• ;
  
• 
  
• 我们先来回顾SQL2000的行列转换,比如我们对上例程把col3转列显示，并把col4的和当对应列值。我们分两种情况来讨论：
  
• 一、当col3的列值固定就是'abc','abcd','ae'三种情况
  
• SELECT
  
• col1,
  
• col2,
  
• [abc]=SUM(CASEWHENcol3='abc'THENcol4ELSE0END),
  
• [abcd]=SUM(CASEWHENcol3='abcd'THENcol4ELSE0END),
  
• [ae]=SUM(CASEWHENcol3='ae'THENcol4ELSE0END)
  
• FROMta
  
• GROUPBYcol1,col2
  
• /*
  
• col1col2abcabcdae
  
• -----------------------------------------
  
• HNCD030
  
• HNCS120
  
• HNHY004
  
• 
  
• (3行受影响)
  
• */
  
• 二、当col3的列值不固定时就运用动态SQL，其实也就是构造一个sum(CASEWHEN...)SQL字符串
  
• DECLARE@svarchar(8000)
  
• SELECT@s=isnull(@s+',
  
• ','')+'['+col3+']=SUM(CASEWHENcol3='''+col3+'''THENcol4ELSE0END)'
  
• FROM(SELECTdistinctcol3FROMta)a
  
• SET@s='SELECT
  
• col1,
  
• col2,
  
• '+@s+'
  
• FROMta
  
• GROUPBY
  
• col1,col2'
  
• EXEC(@s)
  
• /*
  
• col1col2abcabcdae
  
• -----------------------------------------
  
• HNCD030
  
• HNCS120
  
• HNHY004
  
• 
  
• (3行受影响)
  
• */
  
• 我们先输入这个@S看看是什么东东，只要加上print@s
  
• 
  
• SELECT
  
• col1,
  
• col2,
  
• [abc]=SUM(CASEWHENcol3='abc'THENcol4ELSE0END),
  
• [abcd]=SUM(CASEWHENcol3='abcd'THENcol4ELSE0END),
  
• [ae]=SUM(CASEWHENcol3='ae'THENcol4ELSE0END)
  
• FROMta
  
• GROUPBY
  
• col1,col2
  
• 
  
• 其实就是上面我们构造的固定列值的SQL嘛。
  
• 好，现在们开始在2005中实现这个功能，先来看看2005的FROM子句的定义(关于如何看这个定义请参照SQL2005的文档约定及Transate-SQL语法约定)：
  
• [FROM{<table_source>}[,...n]]
  
• <table_source>::=
  
• {
  
• <pivoted_table>
  
• }
  
• <pivoted_table>::=
  
• table_sourcePIVOT<pivot_clause>table_alias
  
• 
  
• <pivot_clause>::=
  
• (aggregate_function(value_column)
  
• FORpivot_column
  
• IN(<column_list>)
  
• )
  
• <column_list>::=
  
• column_name[,...]
  
• 
  
• pivot_column和value_column是PIVOT运算符使用的组合列。PIVOT遵循以下过程获得输出结果集：
  
• 对分组列的input_table执行GROUPBY，为每个组生成一个输出行。
  
• 输出行中的分组列获得input_table中该组的对应列值。
  
• 通过执行以下操作，为每个输出行生成列列表中的列的值：
  
• 针对pivot_column，对上一步在GROUPBY中生成的行另外进行分组。
  
• 对于column_list中的每个输出列，选择满足以下条件的子组：
  
• pivot_column=CONVERT(<datatypeofpivot_column>,'output_column')
  
• 针对此子组上的aggregate_function对value_column求值，其结果作为相应的output_column的值返回。如果该子组为空，SQLServer将为该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：
  
• 
  
• SELECTcol1,col2,[abc],[abcd],[ae]
  
• FROM
  
• (SELECTcol1,col2,col3,col4
  
• FROMta)p
  
• PIVOT
  
• (SUM(col4)
  
• FORcol3IN([abc],[abcd],[ae])
  
• )ASunpvt
  
• 
  
• 我们执行一下看看结果：
  
• /*
  
• col1abcabcdae
  
• -------------------------------------
  
• HN1NULLNULL
  
• HNNULL2NULL
  
• HNNULL3NULL
  
• HNNULLNULL4
  
• 
  
• (4行受影响)
  
• */
  
• 如果我们去掉这些NULL那么可以这样：
  
• SELECTcol1,col2,ISNULL([abc],0)AS[ABC],ISNULL([abcd],0)AS[ABCD],ISNULL([ae],0)AS[AE]
  
• FROM
  
• (SELECTcol1,col2,col3,col4
  
• FROMta)p
  
• PIVOT
  
• (SUM(col4)
  
• FORcol3IN([abc],[abcd],[ae])
  
• )ASunpvt
  
• 
  
• 
  
• /*
  
• 
  
• col1col2ABCABCDAE
  
• -----------------------------------------
  
• HNCD030
  
• HNCS120
  
• HNHY004
  
• 
  
• (3行受影响)
  
• */
  
• 当然在2005中列值不固定时也要用到动态SQL，我们把这个例子完成如下：
  
• DECLARE@sVARCHAR(1000)
  
• SELECT@s=isnull(@s+',','')+'['+ltrim(COL3)+']'
  
• FROM(SELECTDISTINCTcol3FROMta)a
  
• 
  
• EXEC('SELECTcol1,col2,'+@s+'
  
• FROM
  
• (SELECTcol1,col2,COL3,COL4
  
• FROMTA)p
  
• PIVOT
  
• (SUM(COL4)
  
• FORCOL3IN('+@s+')
  
• )ASunpvt')
  
• /*
  
• col1col2abcabcdae
  
• -----------------------------------------
  
• HNCDNULL3NULL
  
• HNCS12NULL
  
• HNHYNULLNULL4
  
• 
  
• (3行受影响)
  
• */
  
• 最后我们再完成一个table_source是多表关联的例子，准备数据如下：
  
• --TestData:ta
  
• Ifobject_id('ta')isnotnull
  
• Droptableta
  
• ;
  
• 
  
• Createtableta(idint,省nvarchar(2),市nvarchar(2),具体货品nvarchar(4))
  
• ;
  
• Insertintota
  
• select1,'HN','CS','abc'unionall
  
• select2,'HN','CS','abcd'unionall
  
• select3,'HN','CD','abcd'unionall
  
• select4,'HN','HY','ae'
  
• ;
  
• --TestData:tb
  
• Ifobject_id('tb')isnotnull
  
• Droptabletb
  
• ;
  
• Createtabletb(编号int,具体货品nvarchar(5),大类别int)
  
• ;
  
• Insertintotb
  
• select1,'abc',1unionall
  
• select2,'abcd',2unionall
  
• select3,'abcde',1unionall
  
• select4,'ae',3
  
• Go
  
• --Start
  
• 
  
• -----2005写法
  
• select@s=isnull(@s+',','')+'['+ltrim(大类别)+']'
  
• from(selectdistincttop100percent大类别fromtborderby大类别)a
  
• 
  
• exec('SELECT省,市,'+@s+'
  
• FROM
  
• (SELECT省,市,大类别,a.编号
  
• FROMtaaleftjointbbona.具体货品=b.具体货品)p
  
• PIVOT
  
• (COUNT(编号)
  
• FOR大类别IN('+@s+')
  
• )ASunpvt')
  
• 
  
• 
  
• --Result:
  
• /*
  
• 
  
• 
  
• (3行受影响)
  
• 
  
• 省市123
  
• -----------------------------------------
  
• HNCD010
  
• HNCS110
  
• HNHY001
  
• 
  
• (3行受影响)
  
• 
  
• */
  
• --End
  
• 
  
• 好，我们对2005的PIVOT这个新功能的学习就到这儿了，多练习就可以熟练的使用这个PIVOT用法。