基础例子
在数据处理的过程中，常常遇到行列转换的问题。例如，人员的考勤。可能表格中，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 ) t1PIVOT(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 ) tPIVOT(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 ) tPIVOT(SUM(myday)FOR mymonth IN (' + @columns + ')) as p ' EXEC(@sql)
实际业务中，可以根据需要，可以创建存储过程，动态列用存储过程包裹起来。
