SQL Server的行转列和列转行

行转列，列转行是我们在开发过程中经常碰到的问题。

行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 的运算符PIVOT来实现。

用传统的方法，比x = a I较好理解。层次清晰，而且比较习惯L F j s T a .。但是PIVOT 、UNPIVOJ W k 9 -T提供的语法比一系列复杂的SELECT…CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。

我们首先先通过一个老生常/ U % : @ ] 3谈的例子，学生成绩表(下面简化了些)来形象了解下行转列

CREATE  TABLE 
(
           NVARCHAR(20),        --学生姓名
             NVARCHAR(30),        --科目
               FLOAT j a { w R d,               --成绩
)

INSERT INTO  SELECT \9 p I'Nick\', \'语文\', 80
INSERT INTO  SELECT \'Nick\', \'数学\', 90
INSERT INTO  SELECT \'Nick\', \'英语\', 70
INSERT INTO  SELER 5 , # 4 gCT \'Nick\', \'生物\', 85
INSERh D J r BT INTOK E H O : Y  SELECT \'C F r Z kKent\', \'语文\', 80
INSERT INTO  SELECT \'Kent\', \'数学\', 90
INSERT INTO  SELECT \'* C 2 5 ^ o 1 }Kent\', \'英语\', 70
INQ Q S pSERT INTO  SELECT \'Kentf ( V O l 6 6\', \'生物\', 85

如果我想知道每位学生的每科成绩，而且每个学生的全部, ? q u 8 4 f 4成绩排c , o R 7(CASE Subject WHEN \'数学\' THEN Score ELSE 0 END) AS \'数学\',
MAX(CASE SubjeJ ; _ct WHEN \'英语\' THEN Score ELSE 0 END) AS \'英语\',
MAX(CASE Subject WHEN \'生物\' THEN Score ELSE 0 ENj d D) AS \'生物\'
FROM dbo.
GROUP BY UserName

查询结果如图所示，这样我们就能很清楚的了解W c T Q ) 4 A M每位学生所有的成绩了

接下来我们来看看第二个小列子。有一个游戏玩家充值$ 6 e p w y表（仅仅为了说明，举的一个小例子），8 u Y u o ` l x

CREATE TABLE 
(
                  INT IDENTITY(1,1),
            NVARCH j - K B Q 7 { 1AR(20),  --游戏玩家
           DATETIME,      --充值时间
              NVARCHAR(20),  --充值类型
                DECIMAL,       --充值金额
            BIT,           --是r [ + o  O N否成功 1表示成功， 0表示失败
   CONSTRAH ` 1 t f r 0 8 KINT  9] PRIMARY KEY(ID)
)
INSERT INTO Inpo 0 / = 5 K G )urs SELECT \} w c {'张三\', \'2010-05-01\', \'支付宝\', 50, 1
INSERT INTO Inpours SELECT \'张三\', \'2010-06-14\', \'支付宝\', 50,e G ; N 0 C 1
INSERT INTO Inpours SELECT \'张三\', \'2010-06-14\', \'手_ ; - a Z 6 H机短信\', 100, 1
INSERT INTO Inpours SR & f c % 5 PELECT \'李四\', \'2010-06-14\', \'手机短信\', 100, 1o t F
INSERT IX _ u )NTO Inpours SELE[ . D ( 5CT \'李四\', \q  w ( ='2010-07-1z B O 8 $4\', \'支付宝\', 100, 1
INSERJ @ M w e { 0 kT INTO Inpours SELECT \'王五\', \Y m . h Q'2010-07-14\', \'工商银行卡\', 100, 1
INSERT INTO Inpours SELECT \'赵六\', \'2010-07-14\', \'建设银行卡\', 100, 1

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

SEV  _ Z T L ? ) mLECT 
      CONVERT(VARCHAR(10), CreateTime, 120) AS Cre$ B k k E ) : |ateTime,
      CASE PayType WHEN \'支付宝\{ H I 6'     THEN SUM(Money) ELSE 0 END AS \$ D M u I  d T ?E PayType WHEN \'手机短信\'    THEN SUb 6 R %M(Money) ELSE 0 END AS \, a S C'手机短信\0 I } V R /',
      CASE PayType WHEN \'工商银a F I _ U行卡\'  THEN SUM(Money) ELS] 1 h T F OE 0 END AS \'工商银行卡l [ n G h\',
      CASE PayType WHEN \'建设银行卡\'  THEN SUM(Money) ELg = S h / 3 e y /SE 0 END AS \'建设银行卡\'
FRO{ w D :  H ^ 2 Rs
GROUP BY CreateTime, PayType

如图所示，我们这样只是: Q I得到了这样的输出结果，还n 0 T 8 S t l需进一步处理，才能得到想要的结果

SELECT
      CreateTime,
      ISNULL(SUM()    , 0)  AS ,
      ISNULL(SUM()  , 0)  AS ,
      ISNULL(SUM(), 0)  AS ,  
      ISNW ` f aULL(SUM(), 0)  AS 
FROM
(
   SELECT 
          CONVERT(VF # Z LARCHAR(10), Creati T {eTime, 120) ASH i v ) p y } Crean h 7teTime,
          CASE PayType WHEN \'支付宝\'     THEN SUM2 : 7 G ~ t u c J(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
GROUj I B R (P BY CreateTime

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

DECLARE @2 d g $ E m (cmdTexW T 3 0 w P +t    VARCHAR(8000);
DECU ? A x & %LARE @tmpSql        VARCHAR(8) t ) | m P f M ^000);
SET @cmdText =Y 6 h j ^ \'SELECT CONVERT(VARCHAR(10), CreateTimD } - q y = } +e, 120) AS CreateTime,\' + CHAR(10);
SELECT @cmdText = @cmdText + \' CASE PayType WHEN \'\h j 4 x z 6'\' +
PayType + \J S e ) = % . . S'\'\' THEN SUM(Money) ELSE 0 END AS \'\'\' + PayType
      + \'\'\',\' + CHAR(10)  FROM (SELECT DISTINCTj s & E R PayType FROM Inpours ) T

SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) --注意这里，如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -12 m 9 A T 5 V)

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

SETt | ) h ` @tmpSql =\'SELECT CreateTime,\' + CHAR(10);
SELECT @tmpSql = @tmpSql + \' ISNULL(SUM(\' + PayType  + \'), 0) AS \'\'\' + 
PayType  + \'\'\',\'  + CHAR(10)
FROM  (SELECTn k ) I H + Y DISTINCT PayType FROM Inpours ) T 

SET @tmpSql = LEFT(@tmpSqg L ^ y 5l, LEN(@tmpSql) -2) + \' FROM (\' + CHAR(10); 

SET @cmdText = @tmpSql + @cmdText + \') T GROUP BY CreateTime \';
PRINT @cR 7 xmdText
EXECUf O 4 - d a oTE (@cU k v &  H 8mdText);a & f b b Q  m

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

SELECT CreateTime,  , , , 
FROM
(
   SELECT CONVERT(VARCHAR(10), Creat+ n ) I GeTime, 120) AS CreateE U OTime,PayType, Money
   FROM Inpours
) P
PIVOT (
           S{ t }  ( w 3 dUM(Money)
           FOR PayType IN
           (, , , ) 
     ) AS T
ORDER BYq ] X | C CreateTim6 & L He

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

消息 325，级别 15，状态 1，^ D { +第 9 行

‘PIVOT’ 附近有语法错误。您可能需要将当2 O y ? A 6前数据库的兼容级别设置为更高的值，以启用此功能。有关存储过程 sp_dbcmptlevel 的信+ Y 3 U a F息，请参见帮B O E 0 x助。

这个是因为：对升级到 SQL Server 2005B ^ ^ $ V / 或更高版本的数据库使用 PIVO` v { , .T 和 UNPIVOT 时，必须将数据库的兼容级别设置为 90 或更高。有关如何设置数据库兼容级别的信息，请参l ? R k r l阅 spa z 1_dbcmptlevel (Transact-SQL)。例如，5 % f E a只需在执行上面脚本前加上 EXEC sp_dbcmptlevel Test, 90; 就OP K a ` `K了， Test 是所在数据* T }库的名称。

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

Create Table ProgrectDeD w k O 8 r u t %tailh x O 1 B G G y
(
   ProgrectName         NVARCHAR(20), --工程名称
   OverseaSupply        INT,          --海外供应商供给数量
   NativeSupply         INT,          --国内供应商供给数量
   SouthSupply          INT,          --南方供应商供给数量
   NorthSupply          INT           --北方供应商供给数量
) 

INSERT INTO ProgrectDetail
SELECT \'A\', 100, 200, 50, 50
UNION ALL
SELECT \p N G c M u'B\', 200, 300, 150, 150
UNION ALL
SELECT \'C\', 159, 400, 20, 320
UNI g ? b W a %ON ALL
SELECT \'D\', 250, 30, 15, 15

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

SELECT  ProgrectName, \'OverseaSupply\' AS Supplier,
      MAX(OverseaSupply) AS \'SupplyNum\'
FROM ProgrectDetail
GROUP BY ProgrectName
UNION ALL
SELECT Progre$ W . s ) D U =ctName, \'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 \'SupplyNO 7 0 d L x T sum\' 
FROM Progrect9 J b P I ; {Detail
GROUP BY ProgrectName

用UNPIVOT 实现如下

SELECT ProgrectName,Supplier,SupplyNum 
FROM  
( 
   SELECT ProgrectName, OverseaSuo ) 0 ]pply, NativeSupply,( U i ; : h B 0 B 
          SouthSupply, NorthSupv U _ { ` tply 
    FROM ProgrectDetaiJ M U , Wl 
)T 
UNPIVOT  
( 
   SupplyNum FOR Supplier IN 
   (OverseaSupply, NativeSupply, SouthSupply, NorthSupply ) 
) P

——End——

案例收集自互联网，分享| ^ z s F技巧，~ ^ Q i d v Y R让学习和工; 6 y作更高效。

SQL Server的行转列和列转行

相关文章