重温SQL——行转列，列转行

下面来了一个统计数据的需求，要求按日期、支付方式来统计充值金额信息。这也是一个典型的行转列的例子。我们可以通过下面的脚本来达到目的

代码

SELECT   CONVERT ( VARCHAR ( 10 ), CreateTime, 120 ) AS CreateTime,
       CASE PayType WHEN   ' 支付宝 '      THEN   SUM ( Money ) ELSE   0   END   AS   ' 支付宝 ' ,
       CASE PayType WHEN   ' 手机短信 '     THEN   SUM ( Money ) ELSE   0   END   AS   ' 手机短信 ' ,
       CASE PayType WHEN   ' 工商银行卡 '   THEN   SUM ( Money ) ELSE   0   END   AS   ' 工商银行卡 ' ,
       CASE PayType WHEN   ' 建设银行卡 '   THEN   SUM ( Money ) ELSE   0   END   AS   ' 建设银行卡 '
FROM Inpours
GROUP   BY CreateTime, PayType

 

如图所示，我们这样只是得到了这样的输出结果，还需进一步处理，才能得到想要的结果

SELECT 

       CreateTime, 

       ISNULL(SUM([支付宝])    , 0)  AS [支付宝]    , 

       ISNULL(SUM([手机短信])  , 0)  AS [手机短信]   , 

       ISNULL(SUM([工商银行卡]), 0)  AS [工商银行卡] ,  

       ISNULL(SUM([建设银行卡]), 0)  AS [建设银行卡]

FROM

(

    SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,

           CASE PayType WHEN '支付宝'     THEN SUM(Money) ELSE 0 END AS '支付宝' ,

           CASE PayType WHEN '手机短信'   THEN SUM(Money) ELSE 0 END AS '手机短信',

           CASE PayType WHEN '工商银行卡' THEN SUM(Money) ELSE 0 END AS '工商银行卡',

           CASE PayType WHEN '建设银行卡' THEN SUM(Money) ELSE 0 END AS '建设银行卡'

    FROM Inpours

    GROUP BY CreateTime, PayType

) T

GROUP BY CreateTime

其实行转列，关键是要理清逻辑，而且对分组（Group by）概念比较清晰。上面两个列子基本上就是行转列的类型了。但是有个问题来了，上面是我为了说明弄的一个简单列子。实际中，可能支付方式特别多，而且逻辑也复杂很多，可能涉及汇率、手续费等等(曾经做个这样一个)，如果支付方式特别多，我们的CASE WHEN 会弄出一大堆，确实比较恼火，而且新增一种支付方式，我们还得修改脚本如果把上面的脚本用动态SQL改写一下，我们就能轻松解决这个问题

代码

DECLARE   @cmdText     VARCHAR ( 8000 );
DECLARE   @tmpSql         VARCHAR ( 8000 );

SET   @cmdText   =   ' SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime, '   +   CHAR ( 10 );
SELECT   @cmdText   =   @cmdText   +   ' CASE PayType WHEN '''   + PayType +   ''' THEN SUM(Money) ELSE 0 END AS '''   + PayType 
                +   ''' , '   +   CHAR ( 10 )  FROM ( SELECT   DISTINCT PayType FROM Inpours ) T

SET   @cmdText   =   LEFT ( @cmdText , LEN ( @cmdText ) - 2 ) -- 注意这里，如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)

SET   @cmdText   =   @cmdText   +   ' FROM Inpours     GROUP BY CreateTime, PayType ' ;

SET   @tmpSql   = ' SELECT CreateTime, '   +   CHAR ( 10 );
SELECT   @tmpSql   =   @tmpSql   +   ' ISNULL(SUM( '   + PayType  +   ' ), 0) AS '''   + PayType  +   ''' , '   +   CHAR ( 10 )
                    FROM   ( SELECT   DISTINCT PayType FROM Inpours ) T

SET   @tmpSql   =   LEFT ( @tmpSql , LEN ( @tmpSql ) - 2 ) +   ' FROM ( '   +   CHAR ( 10 );

SET   @cmdText   =   @tmpSql   +   @cmdText   +   ' ) T GROUP BY CreateTime ' ;
PRINT   @cmdText
EXECUTE ( @cmdText );

 

下面是通过PIVOT来进行行转列的用法，大家可以对比一下，确实要简单、更具可读性（呵呵，习惯的前提下）

代码

SELECT  
        CreateTime, [ 支付宝 ] , [ 手机短信 ] ,
        [ 工商银行卡 ] , [ 建设银行卡 ]
FROM
(
    SELECT   CONVERT ( VARCHAR ( 10 ), CreateTime, 120 ) AS CreateTime,PayType, Money
    FROM Inpours
) P
PIVOT (
            SUM ( Money )
            FOR PayType IN
            ( [ 支付宝 ] , [ 手机短信 ] , [ 工商银行卡 ] , [ 建设银行卡 ] )
      ) AS T
ORDER   BY CreateTime

 

有时可能会出现这样的错误：

消息 325，级别 15，状态 1，第 9 行

'PIVOT' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信息，请参见帮助。

这个是因为：对升级到 SQL Server 2005 或更高版本的数据库使用 PIVOT 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息，请参阅 sp_dbcmptlevel (Transact-SQL)。 例如，只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OK了， Test 是所在数据库的名称。

 

下面我们来看看列转行，主要是通过UNION ALL ,MAX来实现。假如有下面这么一个表

代码

CREATE   TABLE ProgrectDetail
(
    ProgrectName         NVARCHAR ( 20 ), -- 工程名称
    OverseaSupply        INT ,          -- 海外供应商供给数量
    NativeSupply         INT ,          -- 国内供应商供给数量
    SouthSupply          INT ,          -- 南方供应商供给数量
    NorthSupply          INT            -- 北方供应商供给数量
)

INSERT   INTO ProgrectDetail
SELECT   ' A ' , 100 , 200 , 50 , 50
UNION   ALL
SELECT   ' B ' , 200 , 300 , 150 , 150
UNION   ALL
SELECT   ' C ' , 159 , 400 , 20 , 320
UNION   ALL
SELECT   ' D ' , 250 , 30 , 15 , 15

 

我们可以通过下面的脚本来实现，查询结果如下图所示

代码

SELECT ProgrectName, ' OverseaSupply '   AS Supplier,
        MAX (OverseaSupply) AS   ' SupplyNum '
FROM ProgrectDetail
GROUP   BY ProgrectName
UNION   ALL
SELECT ProgrectName, ' NativeSupply '   AS Supplier,
        MAX (NativeSupply) AS   ' SupplyNum '
FROM ProgrectDetail
GROUP   BY ProgrectName
UNION   ALL
SELECT ProgrectName, ' SouthSupply '   AS Supplier,
        MAX (SouthSupply) AS   ' SupplyNum '
FROM ProgrectDetail
GROUP   BY ProgrectName
UNION   ALL
SELECT ProgrectName, ' NorthSupply '   AS Supplier,
        MAX (NorthSupply) AS   ' SupplyNum '
FROM ProgrectDetail
GROUP   BY ProgrectName

 

 

用UNPIVOT 实现如下：

代码

SELECT ProgrectName,Supplier,SupplyNum
FROM  
(
    SELECT ProgrectName, OverseaSupply, NativeSupply,
           SouthSupply, NorthSupply
     FROM ProgrectDetail
)T
UNPIVOT 
(
    SupplyNum FOR Supplier IN
    (OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P

 

 

 

转载于:https://www.cnblogs.com/ajunForNet/p/4513792.html