SQL Server 2008中的Pivot和UnPivot

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]( ----[PKID] int primary key identity(101,1), [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]( ----[PKID] int primary key identity(101,1), [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 */

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
助人等于自助! 3w@live.cn