SQL SERVER PIVOT/UNPIVOT实现行列转换

 1、PIVOT、UNPIVOT介绍

SQL SERVER中，可以使用 PIVOT 和 UNPIVOT关系运算符实现行列转换。

   

PIVOT通过将表达式某一列中的唯一值转换为输出中的多个列来旋转表值表达式，并在必要时对最终输出中所需的任何其余列值执行聚合。

PIVOT运算符能够利用CASE语句查询实现相同的功能，但是可以用更少的代码就实现，而且看起来更漂亮。

PIVOT的语法：SELECT <非透视的列>, [第一个透视的列],...[最后一个透视的列] FROM (<生成数据的 SELECT 查询>)  PIVOT (<聚合函数>(<要聚合的列>) FOR [<包含要成为列标题的值的列>] IN ( [第一个透视的列],... [最后一个透视的列]) ) AS <透视表的别名> <可选的 ORDER BY 子句>。

 

UNPIVOT运算符，PIVOT执行相反的操作，将表值表达式的列名转换为列值（行转列）。相比于直接通过union来实现列转行，使用UNPIVOT可以让sql语句变得更加简洁。相对而言，理解难度会上升，可读性下降。

 

2、PIVOT实现列转行

--创建表，填充数据
CREATE TABLE #TEMP_ROW_TO_COL (NAME VARCHAR(5), COLUMNS VARCHAR(200), VALUE INT);
INSERT INTO #TEMP_ROW_TO_COL
VALUES ('小明', 'Chinese', '10'),
       ('小明', 'English', '20'),
       ('小明', 'Math', '30'),
       ('小明', 'Physics', '40'),
       ('小強', 'Chinese', '60'),
       ('小強', 'English', '70'),
       ('小強', 'Math', '80');

--查看表数据

SELECT * FROM #TEMP_ROW_TO_COL;

--进行列转行
SELECT * FROM #TEMP_ROW_TO_COL PIVOT (MAX(VALUE) FOR COLUMNS IN (Chinese, English, Math, Physics)) AS T;
--SELECT NAME ,Chinese, English, Math, Physics FROM #TEMP_ROW_TO_COL PIVOT (MAX(VALUE) FOR COLUMNS IN (Chinese, English, Math, Physics)) AS T;--等效

--删除表
DROP TABLE #TEMP_ROW_TO_COL;

 

3、UNPIVOT实现行转列 

--创建表，填充数据
CREATE TABLE #TEMP_COL_TO_ROW (NAME VARCHAR(5), Chinese INT, English INT, Math INT, Physics INT);
INSERT INTO #TEMP_COL_TO_ROW VALUES ('小明', 10, 20, 30, 40), ('小強', 60, 70, 80, NULL);

--查看表数据
SELECT * FROM #TEMP_COL_TO_ROW;

--进行行转列
SELECT NAME,COLUMNS,VALUE FROM #TEMP_COL_TO_ROW UNPIVOT (VALUE FOR COLUMNS IN (Chinese, English, Math, Physics)) AS T;

--删除表
DROP TABLE #TEMP_COL_TO_ROW;