sql server stuff行转列_【SQL】行转列

莫听穿林打叶声|何妨吟啸且徐行

数据在数据(仓)库中往往是以行的形式存储居多，一行就是一条记录，比如我们最常用的订单表，一条订单记录代表客户的某一次购买，这条记录会详细记录金额，成交时间等。 但是有的时候为了方便分析，需要把行形式的数据展开成列形式的。

本文主要就是介绍几种行转列的方法，以及我自己由浅入深的思考。

正文

Pre 数据准备

;WITH Student AS(SELECT '刘得华' AS Name ,'语文' AS Course,54 AS GradeUNION ALLSELECT '刘得华' AS Name ,'数学' AS Course,95 AS GradeUNION ALLSELECT '刘得华' AS Name ,'英语' AS Course,60 AS GradeUNION ALLSELECT '吴延祖' AS Name ,'语文' AS Course,98 AS GradeUNION ALLSELECT '吴延祖' AS Name ,'数学' AS Course,47 AS GradeUNION ALLSELECT '吴延祖' AS Name ,'英语' AS Course,75 AS Grade)SELECT * FROM Student

Name	Course	Grade
刘得华	语文	54
刘得华	数学	95
刘得华	英语	60
吴延祖	语文	98
吴延祖	数学	47
吴延祖	英语	75

01 通过case when和聚合函数实现(SQL Server/MySQL/Hive)

SELECT Name,    SUM(CASE Course WHEN '语文' THEN Grade ELSE 0 END) AS '语文',    SUM(CASE Course WHEN '数学' THEN Grade ELSE 0 END) AS '数学',    SUM(CASE Course WHEN '英语' THEN Grade ELSE 0 END) AS '英语'FROM StudentGROUP BY Name

Name	语文	数学	英语
刘得华	54	95	60
吴延祖	98	47	75

02  通过IF和聚合函数实现(MySQL/Hive)

SELECT Name,    SUM(IF(Course='语文',Grade,0)) AS '语文',    SUM(IF(Course='数学',Grade,0)) AS '数学',    SUM(IF(Course='英语',Grade,0)) AS '英语'FROM StudentGROUP BY Name

这里需要注意的是：SQL Server中，单个SELECT语句的结果集不能用IF，但是存储过程中可以使用IF；MySQL和Hive中，IF函数和CASE WHEN这两种用法均可以使用。

03 数据透视表函数PIVOT(SQL Server)

SELECT Name,    [语文],    [数学],    [英语]FROM StudentPIVOT(SUM(Grade) FOR [Course] IN ([语文],[数学],[英语])) ptORDER BY Name DESC

数据透视表函数PIVOT和Excel中的插入透视表的原理相同，不是改变原有的表数据， 而是会生成一个新表，因此需要为其指定表别名(如代码中的pt)。 以下是带批注的PIVOT语法(供参考)

SELECT ,    [第一个透视的列] AS ,    [第二个透视的列] AS ,    ...    [最后一个透视的列] AS ,FROM    TABLE_NAMEPIVOT(    ()FOR[]    IN ([第一个透视的列], [第二个透视的列],    ... [最后一个透视的列])) AS 

三种基本用法已经介绍完啦~

----此处是分割线----

应用的时候难免会遇到各种各样的问题，不至于说程序出错，但是总会忍不住的想：“有没有更简单的方法？”，“有没有一劳永逸的方法？”

场景1 ：如果列字段的数量比较多，比如一个月的每一天，那要把30/31个日期都列出来吗？ 场景2 ：有些业务员1/2/3/4月份有业绩，但是另外一些业务员4/5/6/7/8有业绩，如何统一？

场景3：如果只需要每月的每个周末(周六、周日)的业绩数据呢？

我们发现，以上三个场景，都可以通过把IN后面括号内的内容写活来解决。

FOR [] IN (###)

一般情况下PIVOT函数IN中的内容只能写死，因为IN中不支持子查询，所以这样的话只能用存储过程来解决。

• 创建存储过程

CREATE PROCEDURE PivotColumn ASBEGIN  DECLARE @ColumnNames VARCHAR(100)   DECLARE @selectSQL NVARCHAR(3000)SET @ColumnNames='' --创建游标完成循环DECLARE cur CURSOR LOCAL SCROLL FOR     select distinct Course from Student  DECLARE @Course VARCHAR(100)OPEN cur    FETCH NEXT FROM cur INTO @Course    WHILE @@FETCH_STATUS = 0    BEGIN        --拼接成：[语文],[数学],[英语],        SET @ColumnNames = @ColumnNames + '[' + @Course + '],'    FETCH NEXT FROM cur INTO @Course    ENDCLOSE cur--去掉最后一个英文逗号SET @ColumnNames= LEFT(@ColumnNames, LEN(@ColumnNames)-1)SET @selectSQL = 'SELECT Name,                      [语文],                      [数学],                      [英语]                  FROM Student                  PIVOT                  (                  SUM(Grade) FOR [Course] in ({ColumnNames})                   ) pt                  ORDER BY Name DESC'--用@ColumnNames替换'{ColumnNames}'，得到@selectSQL             SET @selectSQL= REPLACE(@selectSQL,'{ColumnNames}',@ColumnNames)                   --执行sql语句EXEC sp_executesql @selectSQLENDGO

• 执行存储过程

EXEC PivotColumn

END

 欢迎关注“数据分析且徐行”公众号

愿在数据之路上   且行且自在

  实用 | 专业| 干货

我在且徐行等你～