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示例：

基本表数据：

[sql]  view plain  copy

 print ?

1. IF NOT OBJECT_ID('tb_Income') IS NULL  
2.     DROP TABLE [tb_Income]  
3.   
4. /****** Object:  Table [dbo].[tb_Income]   Script Date: 2012/4/5 8:19:21 ******/  
5.   
6. CREATE TABLE [dbo].[tb_Income](  
7. ----[PKID] int primary key identity(101,1),  
8.     [PName] [Nvarchar](20) NOT NULL,  
9.     [CYear] Smallint NOT NULL,  
10.     [CMonth] TinyInt NOT NULL,  
11.     [CMoney] Decimal (10,2)  Not Null  
12.   
13.     )  
14.   
15. GO  
16. INSERT [dbo].[tb_Income]   
17. SELECT '胡一刀',2011,2,5600  
18. union ALL SELECT '胡一刀',2011,1,5678  
19. union ALL SELECT '胡一刀',2011,3,6798  
20. union ALL SELECT '胡一刀',2011,4,7800  
21. union ALL SELECT '胡一刀',2011,5,8899  
22. union ALL SELECT '胡一刀',2011,8,8877  
23. union ALL SELECT '胡一刀',2011,6,7788  
24. union ALL SELECT '胡一刀',2011,7,6798  
25. union ALL SELECT '胡一刀',2011,10,10000  
26. union ALL SELECT '胡一刀',2011,9,12021  
27. union ALL SELECT '胡一刀',2011,11,8799  
28. union ALL SELECT '胡一刀',2011,12,10002  
29.   
30. union ALL SELECT '苗人凤',2011,1,3455  
31. union ALL SELECT '苗人凤',2011,2,4567  
32. union ALL SELECT '苗人凤',2011,3,5676  
33. union ALL SELECT '苗人凤',2011,4,5600  
34. union ALL SELECT '苗人凤',2011,5,6788  
35. union ALL SELECT '苗人凤',2011,6,5679  
36. union ALL SELECT '苗人凤',2011,7,6785  
37. union ALL SELECT '苗人凤',2011,8,7896  
38. union ALL SELECT '苗人凤',2011,9,7890  
39. union ALL SELECT '苗人凤',2011,10,7799  
40. union ALL SELECT '苗人凤',2011,11,9988  
41.   
42. union ALL SELECT '郑希来',2011,2,5600  
43. union ALL SELECT '郑希来',2011,3,2345  
44. union ALL SELECT '郑希来',2011,5,12000  
45. union ALL SELECT '郑希来',2011,4,23456  
46. union ALL SELECT '郑希来',2011,6,4567  
47. union ALL SELECT '郑希来',2011,7,6789  
48. union ALL SELECT '郑希来',2011,8,9998  
49. union ALL SELECT '郑希来',2011,9,34567  
50. union ALL SELECT '郑希来',2011,12,5609  
51.   
52. GO  

测试结果如下：

[sql]  view plain  copy

 print ?

1. SELECT * FROM tb_Income  
2. GO  
3.   
4. /*  
5.   
6. PName    CYear    CMonth    CMoney  
7. 胡一刀    2011    2    5600.00  
8. 胡一刀    2011    1    5678.00  
9. 胡一刀    2011    3    6798.00  
10. 胡一刀    2011    4    7800.00  
11. 胡一刀    2011    5    8899.00  
12. 胡一刀    2011    8    8877.00  
13. 胡一刀    2011    6    7788.00  
14. 胡一刀    2011    7    6798.00  
15. 胡一刀    2011    10    10000.00  
16. 胡一刀    2011    9    12021.00  
17. 胡一刀    2011    11    8799.00  
18. 胡一刀    2011    12    10002.00  
19. 苗人凤    2011    1    3455.00  
20. 苗人凤    2011    2    4567.00  
21. 苗人凤    2011    3    5676.00  
22. 苗人凤    2011    4    5600.00  
23. 苗人凤    2011    5    6788.00  
24. 苗人凤    2011    6    5679.00  
25. 苗人凤    2011    7    6785.00  
26. 苗人凤    2011    8    7896.00  
27. 苗人凤    2011    9    7890.00  
28. 苗人凤    2011    10    7799.00  
29. 苗人凤    2011    11    9988.00  
30. 郑希来    2011    2    5600.00  
31. 郑希来    2011    3    2345.00  
32. 郑希来    2011    5    12000.00  
33. 郑希来    2011    4    23456.00  
34. 郑希来    2011    6    4567.00  
35. 郑希来    2011    7    6789.00  
36. 郑希来    2011    8    9998.00  
37. 郑希来    2011    9    34567.00  
38. 郑希来    2011    12    5609.00  
39. */  

现在需要统计2011年的个人总工资，使用Group by 即可：

[sql]  view plain  copy

 print ?

1. SELECT PName,sum(Cmoney) as YearMoney from tb_Income  
2. GROUP BY PName  
3. ORDER BY sum(Cmoney) desc  
4.   
5. /*  
6. PName    YearMoney  
7. 郑希来    104931.00  
8. 胡一刀    99060.00  
9. 苗人凤    72123.00  
10. */  

现在我们来进行行列转换：

[sql]  view plain  copy

 print ?

1. SELECT CYear,胡一刀,苗人凤,郑希来 FROM tb_Income   
2. PIVOT(sum(CMoney)   
3. FOR PName IN  
4.  (胡一刀,苗人凤,郑希来)) t  
5.   
6.  /*  
7.  CYear    胡一刀    苗人凤    郑希来  
8.     5678.00    3455.00    NULL  
9.     5600.00    4567.00    5600.00  
10.     6798.00    5676.00    2345.00  
11.     7800.00    5600.00    23456.00  
12.     8899.00    6788.00    12000.00  
13.     7788.00    5679.00    4567.00  
14.     6798.00    6785.00    6789.00  
15.     8877.00    7896.00    9998.00  
16.     12021.00    7890.00    34567.00  
17.     10000.00    7799.00    NULL  
18.     8799.00    9988.00    NULL  
19.     10002.00    NULL    5609.00  
20. */  

注意行列已经转换，再汇总,关键是去除干扰列，重新构建新数据集X：

[sql]  view plain  copy

 print ?

1. SELECT 胡一刀,苗人凤,郑希来 FROM   
2. (SELECT PName,CMoney FROM tb_Income) X   
3. PIVOT(sum(CMoney)   
4. FOR PName IN  
5.  (胡一刀,苗人凤,郑希来)) t  
6.   
7.  /*  
8.     胡一刀    苗人凤    郑希来  
9. .00    72123.00    104931.00  
10. */  

UNPIVOT的示例更简单一些：

生成基本数据：

[sql]  view plain  copy

 print ?

1. IF NOT OBJECT_ID('tb_Tel') IS NULL  
2.     DROP TABLE [tb_Tel]  
3.   
4. CREATE TABLE [dbo].[tb_Tel](  
5. ----[PKID] int primary key identity(101,1),  
6.     [PName] [Nvarchar](20) NOT NULL,  
7.     [Mobile1] [Nvarchar](20) NOT NULL,  
8.     [Mobile2] [Nvarchar](20) NOT NULL,  
9.     [Mobile3] [Nvarchar](20) Not Null  
10.     )  
11. GO  
12. INSERT [dbo].[tb_Tel]   
13. SELECT '胡一刀','13067894562','13567889667','16767894562'  
14. union ALL SELECT '苗人凤','1507894562','15267889667','15367894562'  
15. union ALL SELECT '郑希来','18067894562','18567889667','18767894562'  
16. GO  

结果：

[sql]  view plain  copy

 print ?

1. SELECT * FROM tb_Tel  
2.   
3. /*  
4. PName    Mobile1    Mobile2    Mobile3  
5. 胡一刀    13067894562    13567889667    16767894562  
6. 苗人凤    1507894562    15267889667    15367894562  
7. 郑希来    18067894562    18567889667    18767894562  
8. */  

行列转换：

[sql]  view plain  copy

 print ?

1. SELECT PName,电话类型,电话号码  
2. FROM  tb_Tel  
3. UNPIVOT(电话类型 FOR 电话号码 IN (Mobile1,Mobile2,Mobile3) ) p  
4.   
5. /*  
6. PName    电话类型    电话号码  
7. 胡一刀    13067894562    Mobile1  
8. 胡一刀    13567889667    Mobile2  
9. 胡一刀    16767894562    Mobile3  
10. 苗人凤    1507894562    Mobile1  
11. 苗人凤    15267889667    Mobile2  
12. 苗人凤    15367894562    Mobile3  
13. 郑希来    18067894562    Mobile1  
14. 郑希来    18567889667    Mobile2  
15. 郑希来    18767894562    Mobile3  
16. */