[学习SQL SERVER 2005系列]感受新功能二：UNPIVOT
[*][学习SQLSERVER2005系列]感受新功能二：UNPIVOT
[*]
[*]今晚我们就看看SQL2005这个UNPIVOT吧。UNPIVOT几乎完全是PIVOT相反的操作，将列转换为行。它和PIVOT关系运算符一样对表值表达式进行操作以获得另一个表。记得我们在SQL2000中要用UNIONALL来把多列合并到一列的情况吧，同样对于列不定时，我们往往还利用系统表syscolumns来构造动态SQL，然后用EXEC来运行。
[*]环境准备：
[*]------------------------------------
[*]--Author:happyflsytone
[*]--Date:2008-09-2214:05:26
[*]------------------------------------
[*]
[*]--TestData:ta
[*]IFOBJECT_ID('ta')ISNOTNULL
[*]DROPTABLEta
[*]Go
[*]CREATETABLEta(col1nvarchar(2),col2nvarchar(2),Anvarchar(1),Bnvarchar(1),Cnvarchar(1))
[*]Go
[*]INSERTINTOta
[*]select'HN','CD','0','3','0'unionall
[*]select'HN','CS','1','2','0'unionall
[*]select'HN','HY','0','0','4'
[*]GO
[*]
[*]我们先来回顾SQL2000的行列转换,比如我们对上例程把abcg列转行显示。我们分两种情况来讨论：
[*]一、当col3的列值固定就是A、B、C三列的情况
[*]
[*]SELECTCOL1,COL2,NEWCOL='A',A
[*]FROMTA
[*]UNIONALL
[*]SELECTCOL1,COL2,NEWCOL='B',B
[*]FROMTA
[*]UNIONALL
[*]SELECTCOL1,COL2,NEWCOL='C',C
[*]FROMTA
[*]ORDERBYCOL1,COL2,NEWCOL
[*]
[*]/*
[*]COL1COL2NEWCOLA
[*]-------------------------
[*]HNCDA0
[*]HNCDB3
[*]HNCDC0
[*]HNCSA1
[*]HNCSB2
[*]HNCSC0
[*]HNHYA0
[*]HNHYB0
[*]HNHYC4
[*]*/
[*]二、当除COL1COL2外的列很多时如果我们还一个一个写unionall就会很累了，这时我们往往读系统表构造SQL串，其实也就是构造一个select....unionallselect....SQL字符串
[*]DECLARE@SVARCHAR(8000)
[*]SELECT@S=ISNULL(@S+'
[*]UNIONALL','')+'
[*]SELECTCOL1,COL2,NEWCOL='''+NAME+''','+NAME+'ASNEWCOLVFROMTA'
[*]FROMSYS.COLUMNSWHEREOBJECT_ID=OBJECT_ID('TA')ANDNAMENOTIN('COL1','COL2')
[*]PRINT(@S+'
[*]ORDERBYCOL1,COL2,NEWCOL')
[*]/*
[*]COL1COL2NEWCOLNEWCOLV
[*]-------------------------
[*]HNCDA0
[*]HNCDB3
[*]HNCDC0
[*]HNCSA1
[*]HNCSB2
[*]HNCSC0
[*]HNHYA0
[*]HNHYB0
[*]HNHYC4
[*]*/
[*]我们先输入这个@S看看是什么东东，只要加上print@s
[*]
[*]SELECTCOL1,COL2,NEWCOL='A',AASNEWCOLVFROMTA
[*]UNIONALL
[*]SELECTCOL1,COL2,NEWCOL='B',BASNEWCOLVFROMTA
[*]UNIONALL
[*]SELECTCOL1,COL2,NEWCOL='C',CASNEWCOLVFROMTA
[*]ORDERBYCOL1,COL2,NEWCOL
[*]
[*]其实就是上面我们构造的固定列值的SQL嘛。
[*]好，现在们开始在2005中实现这个功能，先来看看2005的FROM子句的定义(关于如何看这个定义请参照SQL2005的文档约定及Transate-SQL语法约定)：
[*]]
[*]<table_source>::=
[*]{
[*]<unpivoted_table>
[*]}
[*]<unpivoted_table>::=
[*]table_sourceUNPIVOT<unpivot_clause>table_alias
[*]
[*]<unpivot_clause>::=
[*](value_columnFORpivot_columnIN(<column_list>))
[*]<column_list>::=
[*]column_name[,...]
[*]
[*]pivot_column和value_column是UNPIVOT运算符使用的组合列。指定输入表从column_list中的多个列缩减为名为pivot_column的单个列。
[*]注意了，我们这儿默认你ABC列的类型是一致的。
[*]
[*]接着我们利用我们开头的例子来理解一下这个FROM子句，很显然我们的对应上面的value_column,我们还假定列会下固定为这三项，那么列对应上面的pivot_column,进而我们应该得出,,是column_name即我们要合并的列，最后我们只要构造一下table_source就可以了，如何构造这个table_source，显然pivot_column和value_column新生成的列，其它就应该是你想要分组的列啦.
[*]我们来总结一下：这个FROM子句是基于table_source对pivot_column进行透视，table_source中pivot_column和value_column列之外的列被称为透视运算符的组合列,而UNPIVOT是对输入表执行列的合并操作，并为每个单列返回一新行(二列包含当前列的列名及列值)，好，我们试着写出这个SQL：
[*]
[*]SELECTcol1,col2,,
[*]FROM
[*](SELECTcol1,col2,A,B,C
[*]FROMta)p
[*]UNPIVOT
[*](NEWCOLV
[*]FORNEWCOLIN(A,B,C)
[*])ASunpvt
[*]
[*]我们执行一下看看结果：(为了使输出好看，我对newcol做了处理，只要把改写成CAST(ASVARCHAR(2))AS即可，至于什么差别大家一试就知道。)
[*]
[*]/*
[*]col1col2NEWCOLNEWCOLV
[*]---------------------
[*]HNCDA0
[*]HNCDB3
[*]HNCDC0
[*]HNCSA1
[*]HNCSB2
[*]HNCSC0
[*]HNHYA0
[*]HNHYB0
[*]HNHYC4
[*]
[*](9行受影响)
[*]*/
[*]
[*]
[*]当然在2005中列值不固定时也要用到动态SQL，我们把这个例子完成如下：
[*]DECLARE@sVARCHAR(1000)
[*]SELECT@s=isnull(@s+',','')+'['+ltrim(NAME)+']'
[*]FROMSYS.COLUMNSWHEREOBJECT_ID=OBJECT_ID('TA')ANDNAMENOTIN('COL1','COL2')
[*];
[*]EXEC('SELECTcol1,col2,NEWCOL,NEWCOLV
[*]FROM
[*](SELECTcol1,col2,'+@s+'
[*]FROMTA)p
[*]UNPIVOT
[*](NEWCOLV
[*]FORNEWCOLIN('+@s+')
[*])ASunpvt')
[*]
[*]
[*]
[*]/*
[*]col1col2NEWCOLNEWCOLV
[*]-----------------------------------------------------------------------------------------------------------------------------------------------
[*]HNCDA0
[*]HNCDB3
[*]HNCDC0
[*]HNCSA1
[*]HNCSB2
[*]HNCSC4
[*]HNHYA0
[*]HNHYB0
[*]HNHYC4
[*]
[*](9行受影响)
[*]
[*]*/
[*]下面我们对特殊情况做点补充，顺便一起来复习一下PIVOT操作。当我们上面的例程中的ABC三列出NULL时，结果会什么样呢，首先们利用PIVOT来生成我所说的这种带有NULL示例数据：
[*]------------------------------------
[*]--Author:happyflsytone
[*]--Date:2008-09-2214:33:20
[*]------------------------------------
[*]
[*]--TestData:ta
[*]IFOBJECT_ID('ta')ISNOTNULL
[*]DROPTABLEta
[*]Go
[*]CREATETABLEta(col1nvarchar(2),col2nvarchar(2),NEWCOLnvarchar(1),NEWCOLVint)
[*]Go
[*]INSERTINTOta
[*]select'HN','CD','A','0'unionall
[*]select'HN','CD','B',nullunionall
[*]select'HN','CD','C','0'unionall
[*]select'HN','CS','A','1'unionall
[*]select'HN','CS','B','2'unionall
[*]select'HN','CS','C',nullunionall
[*]select'HN','HY','A','0'unionall
[*]select'HN','HY','B','0'unionall
[*]select'HN','HY','C','4'
[*]GO
[*]--Start
[*]SELECTcol1,col2,,,
[*]FROM
[*](SELECTCOL1,COL2,newcolv,newcol
[*]FROMTA)P
[*]PIVOT
[*](SUM(NEWCOLV)
[*]FORNEWCOLIN(,,)
[*])ASunpvt
[*]--Result:
[*]/*
[*]col1col2ABC
[*]-----------------------------------------
[*]HNCD0NULL0
[*]HNCS12NULL
[*]HNHY004
[*]
[*](3行受影响)
[*]
[*]*/
[*]--End
[*]
[*]我们看上面的B和C列都有我们所说的NULL出现了，好我们先通过UNPIVOT来把行列转换一下看看结果：
[*]------------------------------------
[*]--Author:happyflsytone
[*]--Date:2008-09-2214:05:26
[*]------------------------------------
[*]
[*]--TestData:ta
[*]IFOBJECT_ID('ta')ISNOTNULL
[*]DROPTABLEta
[*]Go
[*]CREATETABLEta(col1nvarchar(2),col2nvarchar(2),Anvarchar(1),Bnvarchar(1),Cnvarchar(1))
[*]Go
[*]INSERTINTOta
[*]select'HN','CD','0',null,'0'unionall
[*]select'HN','CS','1','2',nullunionall
[*]select'HN','HY','0','0','4'
[*]GO
[*]SELECTcol1,col2,CAST(ASVARCHAR(2))AS,
[*]FROM
[*](SELECTcol1,col2,A,B,C
[*]FROMta)p
[*]UNPIVOT
[*](NEWCOLV
[*]FORNEWCOLIN(A,B,C)
[*])ASunpvt
[*]
[*]
[*]/*
[*]col1col2NEWCOLNEWCOLV
[*]---------------------
[*]HNCDA0
[*]HNCDC0
[*]HNCSA1
[*]HNCSB2
[*]HNHYA0
[*]HNHYB0
[*]HNHYC4
[*]
[*](7行受影响)
[*]*/
[*]很显然转换后的最终结果和我们一起的相比发现少了两行，这两行就是一开始列值有NULL的记录,这就是UNPIVOT的一个特殊的地方：UNPIVOT的输入中的NULL不会显示在输出中。大家一定要注意这一点。
[*]
[*]最后，我们对前一讲的PIVOT和现在这个UNPIVOT进行一个总结，我们说UNPIVOT几乎是PIVOT的的反操作,并不完全是PIVOT的逆操作，为什么说不完全是？刚才上面这个先PIVOT再UNPIVOT后的记录忽略了NULL的情况首先就能说明不完全是反操作，下面我们再通过另一个例程说这个不完全：
[*]
[*]------------------------------------
[*]--Author:happyflsytone
[*]--Date:2008-09-2214:33:20
[*]------------------------------------
[*]
[*]--TestData:ta
[*]IFOBJECT_ID('ta')ISNOTNULL
[*]DROPTABLEta
[*]Go
[*]CREATETABLEta(col1nvarchar(2),col2nvarchar(2),NEWCOLnvarchar(1),NEWCOLVint)
[*]Go
[*]INSERTINTOta
[*]select'HN','CD','A',0unionall
[*]select'HN','CD','B',2unionall
[*]select'HN','CD','C',0unionall
[*]select'HN','CD','C',5unionall
[*]select'HN','CS','A',1unionall
[*]select'HN','CS','B',2unionall
[*]select'HN','CS','B',2unionall
[*]select'HN','CS','C',4unionall
[*]select'HN','HY','A',0unionall
[*]select'HN','HY','A',9unionall
[*]select'HN','HY','B',0unionall
[*]select'HN','HY','C',4
[*]GO
[*]--Start
[*]PRINT'--------------原始数据----------------------------'
[*]SELECT*
[*]FROMTA
[*]
[*]SELECTcol1,col2,,,
[*]FROM
[*](SELECTCOL1,COL2,newcolv,newcol
[*]FROMTA)P
[*]PIVOT
[*](SUM(NEWCOLV)
[*]FORNEWCOLIN(,,)
[*])ASunpvt
[*]--Result:
[*]/*
[*]col1col2ABC
[*]-----------------------------------------
[*]HNCD025
[*]HNCS144
[*]HNHY904
[*]
[*](3行受影响)
[*]
[*]*/
[*]--End
[*]我们接着把上面的结果作为原始数据进行列转成行，
[*]--TestData:ta
[*]IFOBJECT_ID('ta')ISNOTNULL
[*]DROPTABLEta
[*]Go
[*]CREATETABLEta(col1NVARCHAR(2),col2NVARCHAR(2),AINT,BINT,CINT)
[*]Go
[*]INSERTINTOta
[*]SELECT'HN','CD','0',2,5UNIONALL
[*]SELECT'HN','CS','1',4,4UNIONALL
[*]SELECT'HN','HY','9',0,4
[*]GO
[*]--Start
[*]PRINT'--------------还原的原始数据----------------------------'
[*]SELECTcol1,col2,CAST(ASVARCHAR(2))AS,
[*]FROM
[*](SELECTcol1,col2,A,B,C
[*]FROMta)p
[*]UNPIVOT
[*](NEWCOLV
[*]FORNEWCOLIN(A,B,C)
[*])ASunpvt
[*]/*
[*]--------------原始数据----------------------------
[*]col1col2NEWCOLNEWCOLV
[*]-------------------------
[*]HNCDA0
[*]HNCDB2
[*]HNCDC0
[*]HNCDC5
[*]HNCSA1
[*]HNCSB2
[*]HNCSB2
[*]HNCSC4
[*]HNHYA0
[*]HNHYA9
[*]HNHYB0
[*]HNHYC4
[*]
[*](12行受影响)
[*]
[*]--------------还原的原始数据----------------------------
[*]col1col2NEWCOLNEWCOLV
[*]-------------------------
[*]HNCDA0
[*]HNCDB2
[*]HNCDC5
[*]HNCSA1
[*]HNCSB4
[*]HNCSC4
[*]HNHYA9
[*]HNHYB0
[*]HNHYC4
[*]
[*](9行受影响)
[*]
[*]*/
[*]通过原始数据及还原的原始数据对比，我们发现UNPIVT是无法反操作PIVOT操作时运用聚合函数生成的新列的情况，所以我们小结如下：
[*]1、如果PIVOT中使用聚合函数，则计算聚合时将不考虑出现在值列中的任何NULL值;
[*]2、UNPIVOT的输入中的NULL不会显示在输出中;
[*]3、UNPIVOT的输出会无法完全还原PIVOT操作之前输入中可能会含有原始的NULL值;
[*]4、UNPIVOT的输出会无法完全还原PIVOT操作之前输入中各行的明细值(因为用了聚合函数);
[*]
[*]好，我们对2005的UNPIVOT及PIVOT这个新功能的学习就到这儿了。
[*]
[*]
[*]
[*]无枪狙击手
[*]于宁.一个风雨交加的夜晚
页:
[1]