`
- 浏览:
1166145 次
- 性别:
- 来自:
北京
-
[学习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
-
-
- 二、当除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')
-
-
- 我们先输入这个@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语法约定):
- [FROM{<table_source>}[,...n]]
- <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子句,很显然我们的[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:
- SELECTcol1,col2,[NEWCOL],[NEWCOLV]
- FROM
- (SELECTcol1,col2,A,B,C
- FROMta)p
- UNPIVOT
- (NEWCOLV
- FORNEWCOLIN(A,B,C)
- )ASunpvt
- 我们执行一下看看结果:(为了使输出好看,我对newcol做了处理,只要把[NEWCOL]改写成CAST([NEWCOL]ASVARCHAR(2))AS[NEWCOL]即可,至于什么差别大家一试就知道。)
-
-
- 当然在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')
-
-
- 下面我们对特殊情况做点补充,顺便一起来复习一下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,[A],[B],[C]
- FROM
- (SELECTCOL1,COL2,newcolv,newcol
- FROMTA)P
- PIVOT
- (SUM(NEWCOLV)
- FORNEWCOLIN([A],[B],[C])
- )ASunpvt
- --Result:
-
-
- --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([NEWCOL]ASVARCHAR(2))AS[NEWCOL],[NEWCOLV]
- FROM
- (SELECTcol1,col2,A,B,C
- FROMta)p
- UNPIVOT
- (NEWCOLV
- FORNEWCOLIN(A,B,C)
- )ASunpvt
-
-
- 很显然转换后的最终结果和我们一起的相比发现少了两行,这两行就是一开始列值有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,[A],[B],[C]
- FROM
- (SELECTCOL1,COL2,newcolv,newcol
- FROMTA)P
- PIVOT
- (SUM(NEWCOLV)
- FORNEWCOLIN([A],[B],[C])
- )ASunpvt
- --Result:
-
-
- --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([NEWCOL]ASVARCHAR(2))AS[NEWCOL],[NEWCOLV]
- FROM
- (SELECTcol1,col2,A,B,C
- FROMta)p
- UNPIVOT
- (NEWCOLV
- FORNEWCOLIN(A,B,C)
- )ASunpvt
-
-
- 通过原始数据及还原的原始数据对比,我们发现UNPIVT是无法反操作PIVOT操作时运用聚合函数生成的新列的情况,所以我们小结如下:
- 1、如果PIVOT中使用聚合函数,则计算聚合时将不考虑出现在值列中的任何NULL值;
- 2、UNPIVOT的输入中的NULL不会显示在输出中;
- 3、UNPIVOT的输出会无法完全还原PIVOT操作之前输入中可能会含有原始的NULL值;
- 4、UNPIVOT的输出会无法完全还原PIVOT操作之前输入中各行的明细值(因为用了聚合函数);
- 好,我们对2005的UNPIVOT及PIVOT这个新功能的学习就到这儿了。
- 无枪狙击手
- 于宁.一个风雨交加的夜晚
分享到:
Global site tag (gtag.js) - Google Analytics
相关推荐
sqlserver使用UNPIVOT函数列转行
枢纽表达式PIVOT和UNPIVOT的简单用法 sql 列转成行 sql server 2005
SQL行列转换 Pivot UnPivot
SQL_SERVER日期函数详细用法SQL_SERVER日期函数详细用法
主要介绍了SQLServer行列互转实现思路,使用聚合函数pivot/unpivot实现行列互转,感兴趣的小伙伴们可以参考一下
chapter1:background to t-sql query chapter2:single-table queries chapter3:joins chapter4:subqueries chapter5:table expressions chapter6:set operations chapter7:pivot ,unpivot,and groupint sets chapter...
sql 所有查询语句,如何使用全新的派生表 自连接 如何使用全新的pivot 如何使用全新的unpivot 如何使用全新的apply 如何使用全新的except 与intersect 运算符号 如何使用全新的“公用表达式” Compute[by]字句的使用...
主要给大家介绍了关于SQL知识点之列转行Unpivot函数的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用SQL具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
Dynamic SQL: Applications, Performance, and Security in Microsoft SQL Server By 作者: Edward Pollack ISBN-10 书号: 148424317X ISBN-13 书号: 9781484243176 Edition 版本: 2nd ed. 出版日期: 2018-12-28 ...
中文名: SQL袖珍参考手册(第3版,涵盖DB2,MySQL,Oracle,PostgreSQL,SQL Server) 原名: SQL Pocket Guide: A Guide to SQL Usage 作者: Jonathan Gennick 资源格式: PDF 版本: 英文文字版/更新源代码 出版社: O'...
pivot 与 unpivot 函数是SQL05新提供的2个函数 灰常灰常的实用
This book is an introduction and deep-dive into the many uses of dynamic SQL in Microsoft SQL Server. Dynamic SQL is key to large-scale searching based upon user-entered criteria. It’s also useful in...
Oracle 11g中的pivot和unpivot转换操作
博客文章地址:http://blog.csdn.net/pfe_nova/article/details/41357435 SQL2005之后添加的几个排名函数以及透析与反透析关键字,主要有Row_Number、RANK、DENSE_RANK、NTILE函数,pivot以及unpivot关键字
关于UNPIVOT 操作符
Sql语句实现表的行列转换,行转列,列转行
8930 UnPivot Sub Categories
有case when方式和2005之后的内置pivot和unpivot方法来实现,行列互转,可以分为静态互转,动态互转。
从MS Sql Server 2005微软就推出了pivot和unpivot实现行列转换,这极大的方便了我们存储数据和呈现数据。今天就对这两个关键字进行分析,结合实例讲解如何存储数据,如何呈现数据。 例如学生选课和成绩系统中就有...