SQL Server 2008中SQL应用系列--目录索引
今天给新成员讲解PIVOT 和 UNPIVOT示例,顺便整理了一下其用法。这是自SQL Server 2005起提供的新功能。
官方示例:http://msdn.microsoft.com/zh-cn/library/ms177410%28v=sql.105%29.aspx
首先看PIVOT示例:
基本表数据:
- IF NOT OBJECT_ID('tb_Income') IS NULL
- DROP TABLE [tb_Income]
-
- /****** Object: Table [dbo].[tb_Income] Script Date: 2012/4/5 8:19:21 ******/
-
- CREATE TABLE [dbo].[tb_Income](
-
- [PName] [Nvarchar](20) NOT NULL,
- [CYear] Smallint NOT NULL,
- [CMonth] TinyInt NOT NULL,
- [CMoney] Decimal (10,2) Not Null
-
- )
-
- GO
- INSERT [dbo].[tb_Income]
- SELECT '胡一刀',2011,2,5600
- union ALL SELECT '胡一刀',2011,1,5678
- union ALL SELECT '胡一刀',2011,3,6798
- union ALL SELECT '胡一刀',2011,4,7800
- union ALL SELECT '胡一刀',2011,5,8899
- union ALL SELECT '胡一刀',2011,8,8877
- union ALL SELECT '胡一刀',2011,6,7788
- union ALL SELECT '胡一刀',2011,7,6798
- union ALL SELECT '胡一刀',2011,10,10000
- union ALL SELECT '胡一刀',2011,9,12021
- union ALL SELECT '胡一刀',2011,11,8799
- union ALL SELECT '胡一刀',2011,12,10002
-
- union ALL SELECT '苗人凤',2011,1,3455
- union ALL SELECT '苗人凤',2011,2,4567
- union ALL SELECT '苗人凤',2011,3,5676
- union ALL SELECT '苗人凤',2011,4,5600
- union ALL SELECT '苗人凤',2011,5,6788
- union ALL SELECT '苗人凤',2011,6,5679
- union ALL SELECT '苗人凤',2011,7,6785
- union ALL SELECT '苗人凤',2011,8,7896
- union ALL SELECT '苗人凤',2011,9,7890
- union ALL SELECT '苗人凤',2011,10,7799
- union ALL SELECT '苗人凤',2011,11,9988
-
- union ALL SELECT '郑希来',2011,2,5600
- union ALL SELECT '郑希来',2011,3,2345
- union ALL SELECT '郑希来',2011,5,12000
- union ALL SELECT '郑希来',2011,4,23456
- union ALL SELECT '郑希来',2011,6,4567
- union ALL SELECT '郑希来',2011,7,6789
- union ALL SELECT '郑希来',2011,8,9998
- union ALL SELECT '郑希来',2011,9,34567
- union ALL SELECT '郑希来',2011,12,5609
-
- GO
测试结果如下:
- SELECT * FROM tb_Income
- GO
-
- /*
-
- PName CYear CMonth CMoney
- 胡一刀 2011 2 5600.00
- 胡一刀 2011 1 5678.00
- 胡一刀 2011 3 6798.00
- 胡一刀 2011 4 7800.00
- 胡一刀 2011 5 8899.00
- 胡一刀 2011 8 8877.00
- 胡一刀 2011 6 7788.00
- 胡一刀 2011 7 6798.00
- 胡一刀 2011 10 10000.00
- 胡一刀 2011 9 12021.00
- 胡一刀 2011 11 8799.00
- 胡一刀 2011 12 10002.00
- 苗人凤 2011 1 3455.00
- 苗人凤 2011 2 4567.00
- 苗人凤 2011 3 5676.00
- 苗人凤 2011 4 5600.00
- 苗人凤 2011 5 6788.00
- 苗人凤 2011 6 5679.00
- 苗人凤 2011 7 6785.00
- 苗人凤 2011 8 7896.00
- 苗人凤 2011 9 7890.00
- 苗人凤 2011 10 7799.00
- 苗人凤 2011 11 9988.00
- 郑希来 2011 2 5600.00
- 郑希来 2011 3 2345.00
- 郑希来 2011 5 12000.00
- 郑希来 2011 4 23456.00
- 郑希来 2011 6 4567.00
- 郑希来 2011 7 6789.00
- 郑希来 2011 8 9998.00
- 郑希来 2011 9 34567.00
- 郑希来 2011 12 5609.00
- */
现在需要统计2011年的个人总工资,使用Group by 即可:
- SELECT PName,sum(Cmoney) as YearMoney from tb_Income
- GROUP BY PName
- ORDER BY sum(Cmoney) desc
-
- /*
- PName YearMoney
- 郑希来 104931.00
- 胡一刀 99060.00
- 苗人凤 72123.00
- */
现在我们来进行行列转换:
- SELECT CYear,胡一刀,苗人凤,郑希来 FROM tb_Income
- PIVOT(sum(CMoney)
- FOR PName IN
- (胡一刀,苗人凤,郑希来)) t
-
- /*
- CYear 胡一刀 苗人凤 郑希来
- 5678.00 3455.00 NULL
- 5600.00 4567.00 5600.00
- 6798.00 5676.00 2345.00
- 7800.00 5600.00 23456.00
- 8899.00 6788.00 12000.00
- 7788.00 5679.00 4567.00
- 6798.00 6785.00 6789.00
- 8877.00 7896.00 9998.00
- 12021.00 7890.00 34567.00
- 10000.00 7799.00 NULL
- 8799.00 9988.00 NULL
- 10002.00 NULL 5609.00
- */
注意行列已经转换,再汇总,关键是去除干扰列,重新构建新数据集X:
- SELECT 胡一刀,苗人凤,郑希来 FROM
- (SELECT PName,CMoney FROM tb_Income) X
- PIVOT(sum(CMoney)
- FOR PName IN
- (胡一刀,苗人凤,郑希来)) t
-
- /*
- 胡一刀 苗人凤 郑希来
- .00 72123.00 104931.00
- */
UNPIVOT的示例更简单一些:
生成基本数据:
- IF NOT OBJECT_ID('tb_Tel') IS NULL
- DROP TABLE [tb_Tel]
-
- CREATE TABLE [dbo].[tb_Tel](
-
- [PName] [Nvarchar](20) NOT NULL,
- [Mobile1] [Nvarchar](20) NOT NULL,
- [Mobile2] [Nvarchar](20) NOT NULL,
- [Mobile3] [Nvarchar](20) Not Null
- )
- GO
- INSERT [dbo].[tb_Tel]
- SELECT '胡一刀','13067894562','13567889667','16767894562'
- union ALL SELECT '苗人凤','1507894562','15267889667','15367894562'
- union ALL SELECT '郑希来','18067894562','18567889667','18767894562'
- GO
结果:
- SELECT * FROM tb_Tel
-
- /*
- PName Mobile1 Mobile2 Mobile3
- 胡一刀 13067894562 13567889667 16767894562
- 苗人凤 1507894562 15267889667 15367894562
- 郑希来 18067894562 18567889667 18767894562
- */
行列转换:
- SELECT PName,电话类型,电话号码
- FROM tb_Tel
- UNPIVOT(电话类型 FOR 电话号码 IN (Mobile1,Mobile2,Mobile3) ) p
-
- /*
- PName 电话类型 电话号码
- 胡一刀 13067894562 Mobile1
- 胡一刀 13567889667 Mobile2
- 胡一刀 16767894562 Mobile3
- 苗人凤 1507894562 Mobile1
- 苗人凤 15267889667 Mobile2
- 苗人凤 15367894562 Mobile3
- 郑希来 18067894562 Mobile1
- 郑希来 18567889667 Mobile2
- 郑希来 18767894562 Mobile3
- */