SQL Server 2008中的Pivot和UnPivot

分享一下我老师大神的人工智能教程！零基础，通俗易懂！http://blog.csdn.net/jiangjunshow

也欢迎大家转载本篇文章。分享知识，造福人民，实现我们中华民族伟大复兴！

               

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    )GOINSERT [dbo].[tb_Income] SELECT '胡一刀',2011,2,5600union ALL SELECT '胡一刀',2011,1,5678union ALL SELECT '胡一刀',2011,3,6798union ALL SELECT '胡一刀',2011,4,7800union ALL SELECT '胡一刀',2011,5,8899union ALL SELECT '胡一刀',2011,8,8877union ALL SELECT '胡一刀',2011,6,7788union ALL SELECT '胡一刀',2011,7,6798union ALL SELECT '胡一刀',2011,10,10000union ALL SELECT '胡一刀',2011,9,12021union ALL SELECT '胡一刀',2011,11,8799union ALL SELECT '胡一刀',2011,12,10002union ALL SELECT '苗人凤',2011,1,3455union ALL SELECT '苗人凤',2011,2,4567union ALL SELECT '苗人凤',2011,3,5676union ALL SELECT '苗人凤',2011,4,5600union ALL SELECT '苗人凤',2011,5,6788union ALL SELECT '苗人凤',2011,6,5679union ALL SELECT '苗人凤',2011,7,6785union ALL SELECT '苗人凤',2011,8,7896union ALL SELECT '苗人凤',2011,9,7890union ALL SELECT '苗人凤',2011,10,7799union ALL SELECT '苗人凤',2011,11,9988union ALL SELECT '郑希来',2011,2,5600union ALL SELECT '郑希来',2011,3,2345union ALL SELECT '郑希来',2011,5,12000union ALL SELECT '郑希来',2011,4,23456union ALL SELECT '郑希来',2011,6,4567union ALL SELECT '郑希来',2011,7,6789union ALL SELECT '郑希来',2011,8,9998union ALL SELECT '郑希来',2011,9,34567union ALL SELECT '郑希来',2011,12,5609GO

测试结果如下：

SELECT * FROM tb_IncomeGO/*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_IncomeGROUP BY PNameORDER 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    )GOINSERT [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_TelUNPIVOT(电话类型 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

           

给我老师的人工智能教程打call！http://blog.csdn.net/jiangjunshow