SQL 之行转列

在实际的编程过程中,会遇到行转列的情况,

这里列举一下常用的方法

在SQL Server 2005以后, 新增了两个关键字PIVOT ,可以非常完美的解决这个问题.

这个MSDN的原文介绍,见下方链接

使用 PIVOT 和 UNPIVOT - SQL Server | Microsoft Docs

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
    --聚合函数 
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

最重要的是聚合函数,按照什么样的规则来进行,如 count ,sum 函数

eg:

假设我们有个表,里面记录了消费类型及金额,我们先造点数据

CREATE TABLE test
  (
    ID INT IDENTITY(1, 1) ,
    Type NVARCHAR(50) ,
    Cost INT
  );
GO 

INSERT  INTO dbo.test
        ( Type, Cost )
VALUES  ( '吃饭', 11 ),
        ( '饮料', 13 ),
        ( '饮料', 13 ),
        ( '瓜子', 13 );

现在想按照消费的类别进行统计,这就涉及到我一个行转列的情况

按照上面的语法我们可以写出,如下的SQL:

SELECT * FROM (SELECT Cost,Type  FROM test) AS mm 
PIVOT (SUM(Cost) FOR Type IN ([吃饭],[瓜子],[饮料])) AS a

当我们类别不固定比较复杂的时候

可以用之前博客将的函数  for xml path 和 stuff 函数 ,实行不确定的行转列, SQL如下

DECLARE @columName NVARCHAR(MAX)
DECLARE @sql NVARCHAR(max)
SELECT  @columName= STUFF((SELECT '],[' + Type
                FROM    test GROUP BY Type
              FOR
                XML PATH('')),1,2,'')+']'
SET @SQL ='SELECT  * FROM (SELECT Cost,Type  FROM test) AS mm 
PIVOT (SUM(Cost) FOR Type IN ('+@columName+')) AS a' 
PRINT @sql
EXEC (@sql)

这种的效果是一样的如下

当然在2005以前的数据库,我们也可以通过case when 来实现, SQL如下

SELECT MAX(b.吃饭) AS 吃饭, MAX(b.饮料) AS 饮料, MAX(b.瓜子) AS 瓜子 FROM (SELECT  CASE Type
          WHEN '吃饭' THEN SUM(Cost)
          ELSE 0
        END AS '吃饭',
		 CASE Type
          WHEN '饮料' THEN SUM(Cost)
          ELSE 0
        END AS '饮料',
		 CASE Type
          WHEN '瓜子' THEN SUM(Cost)
          ELSE 0
        END AS '瓜子'
FROM    test
GROUP BY Type) AS b ; 

 当然我们也可以通过动态拼接的方式实现,类型不确定,比较麻烦,这里就不再说了

文章至此结束了..

持续更新中.