【SQL SERVER】PIVOT与UNPIVOT之行列转换

lyj00436 / 2024-10-09 / 原文

基础例子

在数据处理的过程中，常常遇到行列转换的问题。例如，人员的考勤。可能表格中，1~12月都在同一个字段，实际中，为了查看方便，同一个人的考勤记录，能在同一行，这样查询起来比较方便（行转列）。或者，表格设计的时候就是1~12月，在其他数据分析时需要将列转行。即类似于以下两张表之间的相互转换。

接下来，我们使用数据如下：

 1 CREATE TABLE t_attendance(
 2     id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,        -- 主键
 3     name nvarchar(255),        -- 姓名
 4     mymonth INT,            -- 月份
 5     myday float                -- 出勤天数
 6 )
 7 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',1,24)
 8 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',2,18)
 9 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',3,21)
10 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',4,22)
11 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',5,21)
12 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',6,19)
13 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',7,23)
14 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',8,22)
15 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',9,21)
16 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',10,19)
17 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',11,21)
18 INSERT INTO t_attendance(name,mymonth,myday) VALUES('张三',12,22)
19 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',1,23)
20 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',2,17)
21 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',3,20)
22 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',4,21)
23 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',5,20)
24 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',6,18)
25 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',7,22)
26 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',8,21)
27 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',9,20)
28 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',10,18)
29 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',11,20)
30 INSERT INTO t_attendance(name,mymonth,myday) VALUES('李四',12,21)

考勤数据

除了表数据（t_attendance），我还创建了（v_attendance）用于演示表格之间行列转换方法。

常规使用方法

行转列时，我们可以使用CASE关键字，计算不同条件下的分组。

 1 SELECT name
 2     ,ISNULL(SUM(CASE  myMonth WHEN 1 THEN myday END ), 0) AS '1月出勤'
 3     ,ISNULL(SUM(CASE  myMonth WHEN 2 THEN myday END ), 0) AS '2月出勤'
 4     ,ISNULL(SUM(CASE  myMonth WHEN 3 THEN myday END ), 0) AS '3月出勤'
 5     ,ISNULL(SUM(CASE  myMonth WHEN 4 THEN myday END ), 0) AS '4月出勤'
 6     ,ISNULL(SUM(CASE  myMonth WHEN 5 THEN myday END ), 0) AS '5月出勤'
 7     ,ISNULL(SUM(CASE  myMonth WHEN 6 THEN myday END ), 0) AS '6月出勤'
 8     ,ISNULL(SUM(CASE  myMonth WHEN 7 THEN myday END ), 0) AS '7月出勤'
 9     ,ISNULL(SUM(CASE  myMonth WHEN 8 THEN myday END ), 0) AS '8月出勤'
10     ,ISNULL(SUM(CASE  myMonth WHEN 9 THEN myday END ), 0) AS '9月出勤'
11     ,ISNULL(SUM(CASE  myMonth WHEN 10 THEN myday END ), 0) AS '10月出勤'
12     ,ISNULL(SUM(CASE  myMonth WHEN 11 THEN myday END ), 0) AS '11月出勤'
13     ,ISNULL(SUM(CASE  myMonth WHEN 12 THEN myday END ), 0) AS '12月出勤'
14 FROM t_attendance
15 GROUP BY name

列传行是，我们可以使用Union all，将各个月份的结果集联合起来。

 1 SELECT *
 2 FROM(
 3     SELECT name, 1 AS mymonth, [1] AS myday
 4     FROM v_attendance
 5     UNION ALL 
 6     SELECT name, 2 AS mymonth, [2] AS myday
 7     FROM v_attendance
 8     UNION ALL 
 9     SELECT name, 3 AS mymonth, [3] AS myday
10     FROM v_attendance
11     UNION ALL 
12     SELECT name, 4 AS mymonth, [4] AS myday
13     FROM v_attendance
14     UNION ALL 
15     SELECT name, 5 AS mymonth, [5] AS myday
16     FROM v_attendance
17     UNION ALL 
18     SELECT name, 6 AS mymonth, [6] AS myday
19     FROM v_attendance
20     UNION ALL 
21     SELECT name, 7 AS mymonth, [7] AS myday
22     FROM v_attendance
23     UNION ALL 
24     SELECT name, 8 AS mymonth, [8] AS myday
25     FROM v_attendance
26     UNION ALL 
27     SELECT name, 9 AS mymonth, [9] AS myday
28     FROM v_attendance
29     UNION ALL 
30     SELECT name, 10 AS mymonth, [10] AS myday
31     FROM v_attendance
32     UNION ALL 
33     SELECT name, 11 AS mymonth, [11] AS myday
34     FROM v_attendance
35     UNION ALL 
36     SELECT name, 12 AS mymonth, [12] AS myday
37     FROM v_attendance
38 ) t 
39 ORDER by name desc

使用PIVOT和UNPIVOT进行行列转换

行列转换中，使用Case WITH和 UION ALL行列转换。相比较PIVOT和UNPIVOT不够直观。如果，采用PIVOT进行行转列，或采用UNPIVOT进行列转换则会简化很多。

 1 -- 行转列
 2 SELECT *
 3 FROM (SELECT name, mymonth,myday FROM t_attendance ) t
 4 PIVOT(
 5     SUM(myday)
 6     FOR mymonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
 7 ) as p
 8 
 9 -- 列转行
10 SELECT name,mymonth,myday
11 FROM v_attendance
12 UNPIVOT(
13     myday For mymonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
14 ) as up

PIVOT不能汇总多个列

现在增加字段outdays，为出差天数。

我们希望行转列后，结果如下。

这里，使用分组的代码如下。

 1 SELECT name
 2     ,ISNULL(SUM(CASE  myMonth WHEN 1 THEN myday END ), 0) AS '1月出勤'
 3     ,ISNULL(SUM(CASE  myMonth WHEN 2 THEN myday END ), 0) AS '2月出勤'
 4     ,ISNULL(SUM(CASE  myMonth WHEN 3 THEN myday END ), 0) AS '3月出勤'
 5     ,ISNULL(SUM(CASE  myMonth WHEN 4 THEN myday END ), 0) AS '4月出勤'
 6     ,ISNULL(SUM(CASE  myMonth WHEN 5 THEN myday END ), 0) AS '5月出勤'
 7     ,ISNULL(SUM(CASE  myMonth WHEN 6 THEN myday END ), 0) AS '6月出勤'
 8     ,ISNULL(SUM(CASE  myMonth WHEN 7 THEN myday END ), 0) AS '7月出勤'
 9     ,ISNULL(SUM(CASE  myMonth WHEN 8 THEN myday END ), 0) AS '8月出勤'
10     ,ISNULL(SUM(CASE  myMonth WHEN 9 THEN myday END ), 0) AS '9月出勤'
11     ,ISNULL(SUM(CASE  myMonth WHEN 10 THEN myday END ), 0) AS '10月出勤'
12     ,ISNULL(SUM(CASE  myMonth WHEN 11 THEN myday END ), 0) AS '11月出勤'
13     ,ISNULL(SUM(CASE  myMonth WHEN 12 THEN myday END ), 0) AS '12月出勤'
14     -- 出差
15      ,ISNULL(SUM(CASE  myMonth WHEN 1 THEN outdays END ), 0) AS '1月出差'
16     ,ISNULL(SUM(CASE  myMonth WHEN 2 THEN outdays END ), 0) AS '2月出差'
17     ,ISNULL(SUM(CASE  myMonth WHEN 3 THEN outdays END ), 0) AS '3月出差'
18     ,ISNULL(SUM(CASE  myMonth WHEN 4 THEN outdays END ), 0) AS '4月出差'
19     ,ISNULL(SUM(CASE  myMonth WHEN 5 THEN outdays END ), 0) AS '5月出差'
20     ,ISNULL(SUM(CASE  myMonth WHEN 6 THEN outdays END ), 0) AS '6月出差'
21     ,ISNULL(SUM(CASE  myMonth WHEN 7 THEN outdays END ), 0) AS '7月出差'
22     ,ISNULL(SUM(CASE  myMonth WHEN 8 THEN outdays END ), 0) AS '8月出差'
23     ,ISNULL(SUM(CASE  myMonth WHEN 9 THEN outdays END ), 0) AS '9月出差'
24     ,ISNULL(SUM(CASE  myMonth WHEN 10 THEN outdays END ), 0) AS '10月出差'
25     ,ISNULL(SUM(CASE  myMonth WHEN 11 THEN outdays END ), 0) AS '11月出差'
26     ,ISNULL(SUM(CASE  myMonth WHEN 12 THEN outdays END ), 0) AS '12月出差'
27 FROM t_attendance
28 GROUP BY name

如果使用PIVOT进行行列转换，不能直接加一个出差的汇总（据说Oracle可以）。

这样会语法错误。如果使用两个PIVOT呢？注意，不能两个字段名一致（要不然没办法区分）。

如果连续使用PIVOT呢？

显然，返回的结果不是我们想要的。原来，执行PIVOT的时候，已经把mymonth2和outdays作为条件列。

这时候，我们也可以分别将出勤和出差的列行转列单独使用PIVOT，然后将两次查询结果拼接起来。

SELECT *  -- 字段应该列出来，这里省略
FROM (
        SELECT *
        FROM (SELECT name, mymonth,myday FROM t_attendance ) t1
        PIVOT(
            SUM(myday)
            FOR mymonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
        ) as p1
    ) a LEFT JOIN (
        SELECT *
        FROM (SELECT name, mymonth,outdays FROM t_attendance ) t
        PIVOT(
            SUM(outdays)
            FOR mymonth IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
        ) as p2
) b ON a.name = b.name

这样的语句显然也不简便。所以，PIVOT和UNPIVOT行列转换只对一个数据汇总时，能够看起来比较易读（当然，这已经解决了大部分问题）、

PIVOT动态列问题

前面的例子，我们行列转换的列数是固定的，如果列的值是动态的呢？（比如产品的系列，人员）。为了演示这种情况，查询考勤记录时，没有3、4月的数据。如果，我们使用子查询，会发现语法错误：

这时候，我们可以使用T-SQL动态语句。将要汇总的列，拼接成合适的字符串（ QUOTENAME 为标识符包裹在[]中的函数）

DECLARE @columns NVARCHAR(MAX) , @sql NVARCHAR(MAX)

SET @columns = STUFF( ( SELECT distinct ',' + QUOTENAME( mymonth) FROM t_attendance  FOR XML PATH('') ),1,1,'' )

SET @sql = '
    SELECT *
    FROM (SELECT name, mymonth,myday FROM t_attendance ) t
    PIVOT(
        SUM(myday)
        FOR mymonth IN (' + @columns + ')
    ) as p
'
EXEC(@sql)