- [学习SQL SERVER 2005系列]感受新功能二:UNPIVOT
- 今晚我们就看看SQL2005这个UNPIVOT吧。UNPIVOT几乎完全是PIVOT相反的操作,将列转换为行。它和PIVOT 关系运算符一样对表值表达式进行操作以获得另一个表。记得我们在SQL2000中要用UNION ALL 来把多列合并到一列的情况吧,同样对于列不定时,我们往往还利用系统表syscolumns来构造动态SQL,然后用EXEC来运行。
- 环境准备:
- ------------------------------------
- -- Author: happyflsytone
- -- Date:2008-09-22 14:05:26
- ------------------------------------
- -- Test Data: ta
- IF OBJECT_ID('ta') IS NOT NULL
- DROP TABLE ta
- Go
- CREATE TABLE ta(col1 nvarchar(2),col2 nvarchar(2),A nvarchar(1),B nvarchar(1),C nvarchar(1))
- Go
- INSERT INTO ta
- select 'HN','CD','0','3','0' union all
- select 'HN','CS','1','2','0' union all
- select 'HN','HY','0','0','4'
- GO
- 我们先来回顾SQL2000的行列转换,比如我们对上例程把abcg列转行显示。我们分两种情况来讨论:
- 一、当col3的列值固定就是A、B、C三列的情况
- SELECT COL1,COL2,NEWCOL = 'A',A
- FROM TA
- UNION ALL
- SELECT COL1,COL2,NEWCOL = 'B',B
- FROM TA
- UNION ALL
- SELECT COL1,COL2,NEWCOL = 'C',C
- FROM TA
- ORDER BY COL1,COL2,NEWCOL
- /*
- COL1 COL2 NEWCOL A
- ---- ---- ------ -----------
- HN CD A 0
- HN CD B 3
- HN CD C 0
- HN CS A 1
- HN CS B 2
- HN CS C 0
- HN HY A 0
- HN HY B 0
- HN HY C 4
- */
- 二、当除COL1 COL2外的列很多时如果我们还一个一个写union all就会很累了,这时我们往往读系统表构造SQL串,其实也就是构造一个select .... union all select .... SQL字符串
- DECLARE @S VARCHAR(8000)
- SELECT @S = ISNULL(@S+'
- UNION ALL ','')+'
- SELECT COL1,COL2,NEWCOL='''+NAME+''' ,'+NAME+' AS NEWCOLV FROM TA '
- FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('TA')AND NAME NOT IN('COL1','COL2')
- PRINT( @S+ '
- ORDER BY COL1,COL2,NEWCOL')
- /*
- COL1 COL2 NEWCOL NEWCOLV
- ---- ---- ------ -----------
- HN CD A 0
- HN CD B 3
- HN CD C 0
- HN CS A 1
- HN CS B 2
- HN CS C 0
- HN HY A 0
- HN HY B 0
- HN HY C 4
- */
- 我们先输入这个@S看看是什么东东,只要加上print @s
- SELECT COL1,COL2,NEWCOL='A' ,A AS NEWCOLV FROM TA
- UNION ALL
- SELECT COL1,COL2,NEWCOL='B' ,B AS NEWCOLV FROM TA
- UNION ALL
- SELECT COL1,COL2,NEWCOL='C' ,C AS NEWCOLV FROM TA
- ORDER BY COL1,COL2,NEWCOL
- 其实就是上面我们构造的固定列值的SQL嘛。
- 好,现在们开始在2005中实现这个功能,先来看看2005的FROM子句的定义(关于如何看这个定义请参照SQL2005的文档约定及Transate-SQL语法约定):
- [ FROM { <table_source> } [ ,...n ] ]
- <table_source> ::=
- {
- <unpivoted_table>
- }
- <unpivoted_table> ::=
- table_source UNPIVOT <unpivot_clause> table_alias
- <unpivot_clause> ::=
- ( value_column FOR pivot_column IN ( <column_list> ) )
- <column_list> ::=
- column_name [ , ... ]
- pivot_column 和 value_column 是 UNPIVOT 运算符使用的组合列。指定输入表从 column_list 中的多个列缩减为名为 pivot_column 的单个列。
- 注意了,我们这儿默认你ABC列的类型是一致的。
- 接着我们利用我们开头的例子来理解一下这个FROM子句,很显然我们的[NEWCOLV]对应上面的value_column,我们还假定列会下固定为这三项,那么列 [NEWCOL] 对应上面的pivot_column,进而我们应该得出[a],[b],[c]是column_name即我们要合并的列,最后我们只要构造一下table_source就可以了,如何构造这个table_source,显然pivot_column 和 value_column新生成的列,其它就应该是你想要分组的列啦.
- 我们来总结一下:这个FROM子句是基于 table_source 对 pivot_column 进行透视,table_source 中 pivot_column 和 value_column 列之外的列被称为透视运算符的组合列,而UNPIVOT 是对输入表执行列的合并操作,并为每个单列返回一新行(二列包含当前列的列名及列值),好,我们试着写出这个SQL:
- SELECT col1,col2,[NEWCOL],[NEWCOLV]
- FROM
- (SELECT col1,col2,A,B,C
- FROM ta ) p
- UNPIVOT
- ( NEWCOLV
- FOR NEWCOL IN (A,B,C)
- )AS unpvt
- 我们执行一下看看结果: (为了使输出好看,我对newcol做了处理,只要把[NEWCOL] 改写成CAST([NEWCOL] AS VARCHAR(2)) AS [NEWCOL]即可,至于什么差别大家一试就知道。)
- /*
- col1 col2 NEWCOL NEWCOLV
- ---- ---- ------ -------
- HN CD A 0
- HN CD B 3
- HN CD C 0
- HN CS A 1
- HN CS B 2
- HN CS C 0
- HN HY A 0
- HN HY B 0
- HN HY C 4
- (9 行受影响)
- */
- 当然在2005中列值不固定时也要用到动态SQL,我们把这个例子完成如下:
- DECLARE @s VARCHAR(1000)
- SELECT @s = isnull(@s + ',','')+ '['+ltrim(NAME)+']'
- FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID('TA')AND NAME NOT IN('COL1','COL2')
- ;
- EXEC('SELECT col1,col2,NEWCOL,NEWCOLV
- FROM
- (SELECT col1,col2,'+@s+'
- FROM TA) p
- UNPIVOT
- ( NEWCOLV
- FOR NEWCOL IN ('+@s+')
- )AS unpvt')
- /*
- col1 col2 NEWCOL NEWCOLV
- ---- ---- -------------------------------------------------------------------------------------------------------------------------------- -------
- HN CD A 0
- HN CD B 3
- HN CD C 0
- HN CS A 1
- HN CS B 2
- HN CS C 4
- HN HY A 0
- HN HY B 0
- HN HY C 4
- (9 行受影响)
- */
- 下面我们对特殊情况做点补充,顺便一起来复习一下PIVOT操作。当我们上面的例程中的ABC三列出NULL时,结果会什么样呢,首先们利用PIVOT来生成我所说的这种带有NULL示例数据:
- ------------------------------------
- -- Author: happyflsytone
- -- Date:2008-09-22 14:33:20
- ------------------------------------
- -- Test Data: ta
- IF OBJECT_ID('ta') IS NOT NULL
- DROP TABLE ta
- Go
- CREATE TABLE ta(col1 nvarchar(2),col2 nvarchar(2),NEWCOL nvarchar(1),NEWCOLV int)
- Go
- INSERT INTO ta
- select 'HN','CD','A','0' union all
- select 'HN','CD','B',null union all
- select 'HN','CD','C','0' union all
- select 'HN','CS','A','1' union all
- select 'HN','CS','B','2' union all
- select 'HN','CS','C',null union all
- select 'HN','HY','A','0' union all
- select 'HN','HY','B','0' union all
- select 'HN','HY','C','4'
- GO
- --Start
- SELECT col1,col2,[A],[B],[C]
- FROM
- (SELECT COL1,COL2,newcolv,newcol
- FROM TA)P
- PIVOT
- ( SUM( NEWCOLV)
- FOR NEWCOL IN([A],[B],[C])
- ) AS unpvt
- --Result:
- /*
- col1 col2 A B C
- ---- ---- ----------- ----------- -----------
- HN CD 0 NULL 0
- HN CS 1 2 NULL
- HN HY 0 0 4
- (3 行受影响)
- */
- --End
- 我们看上面的B和C列都有我们所说的NULL出现了,好我们先通过UNPIVOT来把行列转换一下看看结果:
- ------------------------------------
- -- Author: happyflsytone
- -- Date:2008-09-22 14:05:26
- ------------------------------------
- -- Test Data: ta
- IF OBJECT_ID('ta') IS NOT NULL
- DROP TABLE ta
- Go
- CREATE TABLE ta(col1 nvarchar(2),col2 nvarchar(2),A nvarchar(1),B nvarchar(1),C nvarchar(1))
- Go
- INSERT INTO ta
- select 'HN','CD','0',null,'0' union all
- select 'HN','CS','1','2',null union all
- select 'HN','HY','0','0','4'
- GO
- SELECT col1,col2,CAST([NEWCOL] AS VARCHAR(2)) AS [NEWCOL],[NEWCOLV]
- FROM
- (SELECT col1,col2,A,B,C
- FROM ta ) p
- UNPIVOT
- ( NEWCOLV
- FOR NEWCOL IN (A,B,C)
- )AS unpvt
- /*
- col1 col2 NEWCOL NEWCOLV
- ---- ---- ------ -------
- HN CD A 0
- HN CD C 0
- HN CS A 1
- HN CS B 2
- HN HY A 0
- HN HY B 0
- HN HY C 4
- (7 行受影响)
- */
- 很显然转换后的最终结果和我们一起的相比发现少了两行,这两行就是一开始列值有NULL的记录,这就是UNPIVOT的一个特殊的地方:UNPIVOT 的输入中的 NULL 不会显示在输出中。大家一定要注意这一点。
- 最后,我们对前一讲的PIVOT 和现在这个UNPIVOT进行一个总结,我们说UNPIVOT 几乎是PIVOT的的反操作,并不完全是 PIVOT 的逆操作,为什么说不完全是?刚才上面这个先PIVOT再UNPIVOT后的记录忽略了NULL的情况首先就能说明不完全是反操作,下面我们再通过另一个例程说这个不完全:
- ------------------------------------
- -- Author: happyflsytone
- -- Date:2008-09-22 14:33:20
- ------------------------------------
- -- Test Data: ta
- IF OBJECT_ID('ta') IS NOT NULL
- DROP TABLE ta
- Go
- CREATE TABLE ta(col1 nvarchar(2),col2 nvarchar(2),NEWCOL nvarchar(1),NEWCOLV int)
- Go
- INSERT INTO ta
- select 'HN','CD','A',0 union all
- select 'HN','CD','B',2 union all
- select 'HN','CD','C',0 union all
- select 'HN','CD','C',5 union all
- select 'HN','CS','A',1 union all
- select 'HN','CS','B',2 union all
- select 'HN','CS','B',2 union all
- select 'HN','CS','C',4 union all
- select 'HN','HY','A',0 union all
- select 'HN','HY','A',9 union all
- select 'HN','HY','B',0 union all
- select 'HN','HY','C',4
- GO
- --Start
- PRINT '--------------原始数据----------------------------'
- SELECT *
- FROM TA
- SELECT col1,col2,[A],[B],[C]
- FROM
- (SELECT COL1,COL2,newcolv,newcol
- FROM TA)P
- PIVOT
- ( SUM( NEWCOLV)
- FOR NEWCOL IN([A],[B],[C])
- ) AS unpvt
- --Result:
- /*
- col1 col2 A B C
- ---- ---- ----------- ----------- -----------
- HN CD 0 2 5
- HN CS 1 4 4
- HN HY 9 0 4
- (3 行受影响)
- */
- --End
- 我们接着把上面的结果作为原始数据进行列转成行,
- -- Test Data: ta
- IF OBJECT_ID('ta') IS NOT NULL
- DROP TABLE ta
- Go
- CREATE TABLE ta(col1 NVARCHAR(2),col2 NVARCHAR(2),A INT,B INT,C INT)
- Go
- INSERT INTO ta
- SELECT 'HN','CD','0',2,5 UNION ALL
- SELECT 'HN','CS','1',4,4 UNION ALL
- SELECT 'HN','HY','9',0,4
- GO
- --Start
- PRINT '--------------还原的原始数据----------------------------'
- SELECT col1,col2,CAST([NEWCOL] AS VARCHAR(2)) AS [NEWCOL],[NEWCOLV]
- FROM
- (SELECT col1,col2,A,B,C
- FROM ta ) p
- UNPIVOT
- ( NEWCOLV
- FOR NEWCOL IN (A,B,C)
- )AS unpvt
- /*
- --------------原始数据----------------------------
- col1 col2 NEWCOL NEWCOLV
- ---- ---- ------ -----------
- HN CD A 0
- HN CD B 2
- HN CD C 0
- HN CD C 5
- HN CS A 1
- HN CS B 2
- HN CS B 2
- HN CS C 4
- HN HY A 0
- HN HY A 9
- HN HY B 0
- HN HY C 4
- (12 行受影响)
- --------------还原的原始数据----------------------------
- col1 col2 NEWCOL NEWCOLV
- ---- ---- ------ -----------
- HN CD A 0
- HN CD B 2
- HN CD C 5
- HN CS A 1
- HN CS B 4
- HN CS C 4
- HN HY A 9
- HN HY B 0
- HN HY C 4
- (9 行受影响)
- */
- 通过原始数据及还原的原始数据对比,我们发现UNPIVT是无法反操作PIVOT操作时运用聚合函数生成的新列的情况,所以我们小结如下:
- 1、如果 PIVOT 中使用聚合函数,则计算聚合时将不考虑出现在值列中的任何 NULL 值;
- 2、UNPIVOT 的输入中的 NULL 不会显示在输出中;
- 3、UNPIVOT 的输出会无法完全还原 PIVOT 操作之前输入中可能会含有原始的 NULL 值;
- 4、UNPIVOT 的输出会无法完全还原 PIVOT 操作之前输入中各行的明细值(因为用了聚合函数);
- 好,我们对2005的UNPIVOT及PIVOT这个新功能的学习就到这儿了。
- 无枪狙击手
- 于宁.一个风雨交加的夜晚
[学习SQL SERVER 2005系列]感受新功能二:UNPIVOT
最新推荐文章于 2013-08-30 11:04:00 发布