T-SQL行列相互转换命令：PIVOT和UNPIVOT使用详解

一、使用PIVOT和UNPIVOT命令的SQL Server版本要求

创新互联公司专业为企业提供丰县网站建设、丰县做网站、丰县网站设计、丰县网站制作等企业网站建设、网页设计与制作、丰县企业网站模板建站服务，十余年丰县做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。

1.数据库的最低版本要求为SQL Server 2005 或更高。

2.必须将数据库的兼容级别设置为90 或更高。

3.查看我的数据库版本及兼容级别。

如果不知道怎么看数据库版本或兼容级别的话可以在SQL Server Management Studio新建一个查询窗口输入：print @@version，运行之后在我的本机上得到：

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)

Apr  2 2010 15:53:02

Copyright (c) Microsoft Corporation

Express Edition with Advanced Services on Windows NT 5.2 (Build 3790: Service Pack 2)

然后我们选择一个数据库然后右键-属性 选择[选项]得到下图的信息。

在确认数据库的版本和兼容级别符合1,2点的要求后你才可以接着继续往下学习。

二、使用PIVOT 实现数据表的列转行

1.在这里我们先构建一个测试数据表(这里使用的是临时表，以方便我们在退出会话的时候自动删除表及其数据)

首先我们先设计一个表架构为#Student { 学生编号[PK],  姓名, 性别, 所属班级 }的表,然后编写如下T-SQL

--创建临时表(仅演示,表结构的不合理还请包涵)

 
 
 
 
    
  
  
  
  
  1. CREATE TABLE #Student ( 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. [学生编号] INT IDENTITY(1, 1) PRIMARY KEY, 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. [姓名] NVARCHAR(20), 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. [性别] NVARCHAR(1), 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. [所属班级] NVARCHAR(20) 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. );
    12.

--给临时表插入数据

 
 
 
 
    
  
  
  
  
  1. INSERT INTO #Student ( 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. [姓名], [性别], [所属班级] 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. ) 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. SELECT '李妹妹', '女', '初一 1班' UNION ALL 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. SELECT '泰强', '男', '初一 1班' UNION ALL 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. SELECT '泰映', '男', '初一 1班' UNION ALL 
    12. 
  
  
  
  
  12. 
  
  
  
  
  13. SELECT '何谢', '男', '初一 1班' UNION ALL 
    14. 
  
  
  
  
  14. 
  
  
  
  
  15. SELECT '李春', '男', '初二 1班' UNION ALL 
    16. 
  
  
  
  
  16. 
  
  
  
  
  17. SELECT '吴歌', '男', '初二 1班' UNION ALL 
    18. 
  
  
  
  
  18. 
  
  
  
  
  19. SELECT '林纯', '男', '初二 1班' UNION ALL 
    20. 
  
  
  
  
  20. 
  
  
  
  
  21. SELECT '徐叶', '女', '初二 1班' UNION ALL 
    22. 
  
  
  
  
  22. 
  
  
  
  
  23. SELECT '龙门', '男', '初三 1班' UNION ALL 
    24. 
  
  
  
  
  24. 
  
  
  
  
  25. SELECT '小红', '女', '初三 1班' UNION ALL 
    26. 
  
  
  
  
  26. 
  
  
  
  
  27. SELECT '小李', '男', '初三 1班' UNION ALL 
    28. 
  
  
  
  
  28. 
  
  
  
  
  29. SELECT '小黄', '女', '初三 2班' UNION ALL 
    30. 
  
  
  
  
  30. 
  
  
  
  
  31. SELECT '旺财', '男', '初三 2班' UNION ALL 
    32. 
  
  
  
  
  32. 
  
  
  
  
  33. SELECT '强强', '男', '初二 1班';
    34.

以下是查询的结果：

学生编号

姓名

性别

所属班级

1

李妹妹

女

初一 1班

2

泰强

男

初一 1班

3

泰映

男

初一 1班

4

何谢

男

初一 1班

5

李春

男

初二 1班

6

吴歌

男

初二 1班

7

林纯

男

初二 1班

8

徐叶

女

初二 1班

9

龙门

男

初三 1班

10

小红

女

初三 1班

11

小李

男

初三 1班

12

小黄

女

初三 2班

13

旺财

男

初三 2班

14

强强

男

初二 1班

2.查询各班级的总人数

 
 
 
 
    
  
  
  
  
  1. SELECT 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. [所属班级] AS [班级], 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. COUNT(1) AS [人数] 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. FROM #Student 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. GROUP BY [所属班级] 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. ORDER BY [人数] DESC
    12.

班级

人数

初二 1班

5

初一 1班

4

初三 1班

3

初三 2班

2

好了，在这里我希望把上面的表{ 班级, 人数 } 由 班级[行] 的显示转换为 班级[列] 的显示格式!

在此你会看到第一个PIVOT示例。是否很期待??

3.编写第一个PIVOT示例

 
 
 
 
    
  
  
  
  
  1. SELECT 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. '班级总人数:' AS [总人数], 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. [初一 1班], [初一 2班], 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. [初二 1班], 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. [初三 1班], [初三 2班] 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. FROM ( 
    12. 
  
  
  
  
  12. 
  
  
  
  
  13. SELECT 
    14. 
  
  
  
  
  14. 
  
  
  
  
  15. [所属班级] AS [班级], 
    16. 
  
  
  
  
  16. 
  
  
  
  
  17. [学生编号] 
    18. 
  
  
  
  
  18. 
  
  
  
  
  19. FROM #Student 
    20. 
  
  
  
  
  20. 
  
  
  
  
  21. ) AS [SourceTable] 
    22. 
  
  
  
  
  22. 
  
  
  
  
  23. PIVOT ( 
    24. 
  
  
  
  
  24. 
  
  
  
  
  25. COUNT([学生编号]) 
    26. 
  
  
  
  
  26. 
  
  
  
  
  27. FOR [班级] IN ( 
    28. 
  
  
  
  
  28. 
  
  
  
  
  29. [初一 1班], [初一 2班], 
    30. 
  
  
  
  
  30. 
  
  
  
  
  31. [初二 1班], 
    32. 
  
  
  
  
  32. 
  
  
  
  
  33. [初三 1班], [初三 2班] 
    34. 
  
  
  
  
  34. 
  
  
  
  
  35. ) 
    36. 
  
  
  
  
  36. 
  
  
  
  
  37. ) AS [PivotTable]
    38.

在结果表中我们看到了对于不存在的班级初一2班它的总人数为0，这符合我们预期的结果!

解释：使用POVIT首先你需要在FROM子句内定义2个表：

A.一个称为源表(SourceTable)。

B.另一个称为数据透视表(PivotTable)。

语法：

 
 
 
 
    
  
  
  
  
  1. SELECT 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. <未透视的列>, 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. [第一个透视列] AS <列别名>, 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. [第二个透视列] AS <列别名>, 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. ... 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. [最后一个透视列] AS <列别名>
    12. 
  
  
  
  
  12. 
  
  
  
  
  13. FROM ( 
    14. 
  
  
  
  
  14. 
  
  
  
  
    16. 
  
  
  
  
  16. 
  
  
  
  
  17. ) AS <源表>
    18. 
  
  
  
  
  18. 
  
  
  
  
  19. PIVOT ( 
    20. 
  
  
  
  
  20. 
  
  
  
  
  21. <聚合函数>(<列>) 
    22. 
  
  
  
  
  22. 
  
  
  
  
  23. FOR [<需要转换为行的列>] IN ( 
    24. 
  
  
  
  
  24. 
  
  
  
  
  25. [第一个透视列], [第二个透视列], 
    26. 
  
  
  
  
  26. 
  
  
  
  
  27. ... 
    28. 
  
  
  
  
  28. 
  
  
  
  
  29. [最后一个透视列] 
    30. 
  
  
  
  
  30. 
  
  
  
  
  31. ) 
    32. 
  
  
  
  
  32. 
  
  
  
  
  33. ) AS <数据透视表>
    34. 
  
  
  
  
  34. 
  
  
  
  
  35. <可选的ORDER BY子句>;
    36.

以上的PIVOT子句内的第1…n个透视列的值均为需要转换为行的列的常量值,需要用[]括起，支持GUID，字符串及各种数字!

4.下面演示一个较为高级的行转列的应用示例

--使用PIVOT查询班级内的男女学生人数及总人数

 
 
 
 
    
  
  
  
  
  1. SELECT 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. [所属班级] AS [班级], 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. [男] AS [男生人数], 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. [女] AS [女生人数], 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. [男] + [女] AS [总人数] 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. FROM ( 
    12. 
  
  
  
  
  12. 
  
  
  
  
  13. SELECT [学生编号], [所属班级], [性别] FROM #Student 
    14. 
  
  
  
  
  14. 
  
  
  
  
  15. ) AS [SourceTable] 
    16. 
  
  
  
  
  16. 
  
  
  
  
  17. PIVOT ( 
    18. 
  
  
  
  
  18. 
  
  
  
  
  19. COUNT([学生编号]) 
    20. 
  
  
  
  
  20. 
  
  
  
  
  21. FOR [性别] IN ( 
    22. 
  
  
  
  
  22. 
  
  
  
  
  23. [男], [女] 
    24. 
  
  
  
  
  24. 
  
  
  
  
  25. ) 
    26. 
  
  
  
  
  26. 
  
  
  
  
  27. ) AS [PivotTable] 
    28. 
  
  
  
  
  28. 
  
  
  
  
  29. ORDER BY [总人数] DESC
    30.

#p#

三、使用UNPIVOT 实现的功能其实与PIVOT恰恰相反

1.语法同PIVOT但是UNPIVOT的子句没有聚合函数

 
 
 
 
    
  
  
  
  
  1. SELECT 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. <未逆透视的列>, 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. [合并后的列] AS <列别名>, 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. [行值的列名] AS <列别名>
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. FROM ( 
    10. 
  
  
  
  
  10. 
  
  
  
  
    12. 
  
  
  
  
  12. 
  
  
  
  
  13. ) AS <源表>
    14. 
  
  
  
  
  14. 
  
  
  
  
  15. UNPIVOT ( 
    16. 
  
  
  
  
  16. 
  
  
  
  
  17. <行值的列名>
    18. 
  
  
  
  
  18. 
  
  
  
  
  19. FOR <将原来多个列合并到单个列的列名> IN ( 
    20. 
  
  
  
  
  20. 
  
  
  
  
  21. [第一个合并列], [第二个合并列], 
    22. 
  
  
  
  
  22. 
  
  
  
  
  23. ... 
    24. 
  
  
  
  
  24. 
  
  
  
  
  25. [最后一个合并列] 
    26. 
  
  
  
  
  26. 
  
  
  
  
  27. ) 
    28. 
  
  
  
  
  28. 
  
  
  
  
  29. ) AS <数据逆透视表>
    30. 
  
  
  
  
  30. 
  
  
  
  
  31. <可选的ORDER BY子句>;
    32.

2.看上面的语法感觉很浮云，不怕，这里带例子(继续使用II中用到的PIVOT表)

--源表

 
 
 
 
    
  
  
  
  
  1. SELECT 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. '班级总人数:' AS [总人数], 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. [初一 1班], [初一 2班], 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. [初二 1班], 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. [初三 1班], [初三 2班] 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. INTO #PivotTable --为了使表达意图更清晰,我把PIVOT处理后的表放到一个临时表当中 
    12. 
  
  
  
  
  12. 
  
  
  
  
  13. FROM ( 
    14. 
  
  
  
  
  14. 
  
  
  
  
  15. SELECT 
    16. 
  
  
  
  
  16. 
  
  
  
  
  17. [所属班级] AS [班级], 
    18. 
  
  
  
  
  18. 
  
  
  
  
  19. [学生编号] 
    20. 
  
  
  
  
  20. 
  
  
  
  
  21. FROM #Student 
    22. 
  
  
  
  
  22. 
  
  
  
  
  23. ) AS [SourceTable] 
    24. 
  
  
  
  
  24. 
  
  
  
  
  25. PIVOT ( 
    26. 
  
  
  
  
  26. 
  
  
  
  
  27. COUNT([学生编号]) 
    28. 
  
  
  
  
  28. 
  
  
  
  
  29. FOR [班级] IN ( 
    30. 
  
  
  
  
  30. 
  
  
  
  
  31. [初一 1班], [初一 2班], 
    32. 
  
  
  
  
  32. 
  
  
  
  
  33. [初二 1班], 
    34. 
  
  
  
  
  34. 
  
  
  
  
  35. [初三 1班], [初三 2班] 
    36. 
  
  
  
  
  36. 
  
  
  
  
  37. ) 
    38. 
  
  
  
  
  38. 
  
  
  
  
  39. ) AS [PivotTable]
    40.

将多个列合并到单个列的转换的语句!!!

--结果

 
 
 
 
    
  
  
  
  
  1. SELECT 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. [班级], [总人数] 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. FROM ( 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. SELECT 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. [初一 1班], [初一 2班], 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. [初二 1班], 
    12. 
  
  
  
  
  12. 
  
  
  
  
  13. [初三 1班], [初三 2班] 
    14. 
  
  
  
  
  14. 
  
  
  
  
  15. FROM 
    16. 
  
  
  
  
  16. 
  
  
  
  
  17. #PivotTable 
    18. 
  
  
  
  
  18. 
  
  
  
  
  19. ) AS [s] 
    20. 
  
  
  
  
  20. 
  
  
  
  
  21. UNPIVOT ( 
    22. 
  
  
  
  
  22. 
  
  
  
  
  23. [总人数] 
    24. 
  
  
  
  
  24. 
  
  
  
  
  25. FOR [班级] IN ( 
    26. 
  
  
  
  
  26. 
  
  
  
  
  27. [初一 1班], [初一 2班], 
    28. 
  
  
  
  
  28. 
  
  
  
  
  29. [初二 1班], 
    30. 
  
  
  
  
  30. 
  
  
  
  
  31. [初三 1班], [初三 2班] 
    32. 
  
  
  
  
  32. 
  
  
  
  
  33. ) 
    34. 
  
  
  
  
  34. 
  
  
  
  
  35. ) AS [un_p]
    36.

执行下面代码：

 
 
 
 
    
  
  
  
  
  1. SELECT 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. [所属班级] AS [班级], 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. [男] AS [男生人数], 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. [女] AS [女生人数], 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. [男] + [女] AS [总人数] 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. INTO #PivotTable2 --放到临时表方便查询 
    12. 
  
  
  
  
  12. 
  
  
  
  
  13. FROM ( 
    14. 
  
  
  
  
  14. 
  
  
  
  
  15. SELECT [学生编号], [所属班级], [性别] FROM #Student 
    16. 
  
  
  
  
  16. 
  
  
  
  
  17. ) AS [SourceTable] 
    18. 
  
  
  
  
  18. 
  
  
  
  
  19. PIVOT ( 
    20. 
  
  
  
  
  20. 
  
  
  
  
  21. COUNT([学生编号]) 
    22. 
  
  
  
  
  22. 
  
  
  
  
  23. FOR [性别] IN ( 
    24. 
  
  
  
  
  24. 
  
  
  
  
  25. [男], [女] 
    26. 
  
  
  
  
  26. 
  
  
  
  
  27. ) 
    28. 
  
  
  
  
  28. 
  
  
  
  
  29. ) AS [PivotTable] 
    30. 
  
  
  
  
  30. 
  
  
  
  
  31. ORDER BY [总人数] DESC 
    32. 
  
  
  
  
  32. 
  
  
  
  
  33. SELECT 
    34. 
  
  
  
  
  34. 
  
  
  
  
  35. [班级], 
    36. 
  
  
  
  
  36. 
  
  
  
  
  37. [男生或女生人数], 
    38. 
  
  
  
  
  38. 
  
  
  
  
  39. [性别], 
    40. 
  
  
  
  
  40. 
  
  
  
  
  41. [总人数] 
    42. 
  
  
  
  
  42. 
  
  
  
  
  43. FROM ( 
    44. 
  
  
  
  
  44. 
  
  
  
  
  45. SELECT [班级], [男生人数], [女生人数], [总人数] FROM #PivotTable2 
    46. 
  
  
  
  
  46. 
  
  
  
  
  47. ) AS [s] 
    48. 
  
  
  
  
  48. 
  
  
  
  
  49. UNPIVOT ( 
    50. 
  
  
  
  
  50. 
  
  
  
  
  51. [男生或女生人数] 
    52. 
  
  
  
  
  52. 
  
  
  
  
  53. FOR [性别] IN ( 
    54. 
  
  
  
  
  54. 
  
  
  
  
  55. [男生人数], 
    56. 
  
  
  
  
  56. 
  
  
  
  
  57. [女生人数] 
    58. 
  
  
  
  
  58. 
  
  
  
  
  59. ) 
    60. 
  
  
  
  
  60. 
  
  
  
  
  61. ) AS [un_p]
    62.

或者将性别和人数合并到一个列当中：

 
 
 
 
    
  
  
  
  
  1. SELECT 
    2. 
  
  
  
  
  2. 
  
  
  
  
  3. [班级], 
    4. 
  
  
  
  
  4. 
  
  
  
  
  5. [性别] + ': ' + CAST([男生或女生人数] AS NVARCHAR(1)) AS [男生或女生人数], 
    6. 
  
  
  
  
  6. 
  
  
  
  
  7. [总人数] 
    8. 
  
  
  
  
  8. 
  
  
  
  
  9. FROM ( 
    10. 
  
  
  
  
  10. 
  
  
  
  
  11. SELECT [班级], [男生人数], [女生人数], [总人数] FROM #PivotTable2 
    12. 
  
  
  
  
  12. 
  
  
  
  
  13. ) AS [s] 
    14. 
  
  
  
  
  14. 
  
  
  
  
  15. UNPIVOT ( 
    16. 
  
  
  
  
  16. 
  
  
  
  
  17. [男生或女生人数] 
    18. 
  
  
  
  
  18. 
  
  
  
  
  19. FOR [性别] IN ( 
    20. 
  
  
  
  
  20. 
  
  
  
  
  21. [男生人数], 
    22. 
  
  
  
  
  22. 
  
  
  
  
  23. [女生人数] 
    24. 
  
  
  
  
  24. 
  
  
  
  
  25. ) 
    26. 
  
  
  
  
  26. 
  
  
  
  
  27. ) AS [un_p]
    28.

关于PIVOT和UNPIVOT命令的使用就介绍到这里，如果想了解更多SQL的知识可以去看看这里的文章：http://database./sqlserver/，绝对不会让您失望的哦！

网页标题：T-SQL行列相互转换命令：PIVOT和UNPIVOT使用详解
路径分享：http://www.36103.cn/qtweb/news45/37195.html

成都网站建设公司_创新互联，为您提供云服务器、Google、关键词优化、品牌网站建设、网站制作、服务器托管

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

网站建设知识

同城分类信息

成都网站建设　　 　简阳网站制作　　 　网站制作　　 　专业网站设计　　 　四川成都seo网站优化　　 　成都网站建设　　 　成都网站维护　　 　玄科环保　　 　攀枝花网站建设　　 　Android开发　　 　重庆小程序开发公司　　 　成都服务器机柜租用　　 　成都模板网站　　 　成都网络推广　　 　艾名斯智能　　 　德阳服务器托管　　 　成都网络公司　　 　营销型网站建设　　 　广安网站建设　　 　响应式网站建设