SQL数据库查询实现行转列与列转行结果SQL语句(适用于SqlServer数据库，oracle需要修改case when语句)

来源：互联网发布：周杨青的淘宝店铺编辑：程序博客网时间：2024/06/07 09:51

文章来源：http://blog.csdn.net/zhangshengboy/article/details/6431724

行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。用传统的方法，比较好理解。层次清晰，而且比较习惯。但是PIVOT 、UNPIVOT提供的语法比一系列复杂的 SELECT...CASE 语句中所指定的语法更简单、更具可读性。下面我们通过几个简单的例子来介绍一下列转行、行转列问题。

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

CREATE TABLE[StudentScores]
(
    [UserName]        NVARCHAR(20),       --学生姓名
    [Subject]         NVARCHAR(30),       --科目
    [Score]           FLOAT,              --成绩
)

INSERT INTO [StudentScores]SELECT'Nick','语文',80

INSERT INTO [StudentScores]SELECT'Nick','数学',90

INSERT INTO [StudentScores]SELECT'Nick','英语',70

INSERT INTO [StudentScores]SELECT'Nick','生物',85

INSERT INTO [StudentScores]SELECT'Kent','语文',80

INSERT INTO [StudentScores]SELECT'Kent','数学',90

INSERT INTO [StudentScores]SELECT'Kent','英语',70

INSERT INTO [StudentScores]SELECT'Kent','生物',85

如果我想知道每位学生的每科成绩，而且每个学生的全部成绩排成一行，这样方便我查看、统计，导出数据

SELECT
      UserName,
      MAX(CASE SubjectWHEN'语文'THEN ScoreELSE 0END)AS'语文',
      MAX(CASE SubjectWHEN'数学'THEN ScoreELSE 0END)AS'数学',
      MAX(CASE SubjectWHEN'英语'THEN ScoreELSE 0END)AS'英语',
      MAX(CASE SubjectWHEN'生物'THEN ScoreELSE 0END)AS'生物'
FROM dbo.[StudentScores]
GROUP BY UserName

查询结果如图所示，这样我们就能很清楚的了解每位学生所有的成绩了

接下来我们来看看第二个小列子。有一个游戏玩家充值表（仅仅为了说明，举的一个小例子），

Code[http://www.oeedu.com]

CREATE TABLE [Inpours](    [ID]             INT IDENTITY(1,1),     [UserName]          NVARCHAR(20),  --游戏玩家    [CreateTime]     DATETIME,      --充值时间        [PayType]         NVARCHAR(20),  --充值类型        [Money]             DECIMAL,       --充值金额    [IsSuccess]         BIT,           --是否成功 1表示成功， 0表示失败    CONSTRAINT [PK_Inpours_ID] PRIMARY KEY(ID))INSERT INTO Inpours SELECT '张三', '2010-05-01', '支付宝', 50, 1INSERT INTO Inpours SELECT '张三', '2010-06-14', '支付宝', 50, 1INSERT INTO Inpours SELECT '张三', '2010-06-14', '手机短信', 100, 1INSERT INTO Inpours SELECT '李四', '2010-06-14', '手机短信', 100, 1INSERT INTO Inpours SELECT '李四', '2010-07-14', '支付宝', 100, 1INSERT INTO Inpours SELECT '王五', '2010-07-14', '工商银行卡', 100, 1INSERT INTO Inpours SELECT '赵六', '2010-07-14', '建设银行卡', 100, 1

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

Code[http://www.oeedu.com]

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 InpoursGROUP 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 PayTypeWHEN'支付宝'    THENSUM(Money)ELSE0ENDAS'支付宝',
           CASE PayTypeWHEN'手机短信'  THENSUM(Money)ELSE0ENDAS'手机短信',
           CASE PayTypeWHEN'工商银行卡'THENSUM(Money)ELSE0ENDAS'工商银行卡',
           CASE PayTypeWHEN'建设银行卡'THENSUM(Money)ELSE0ENDAS'建设银行卡'
    FROM Inpours
    GROUP BY CreateTime, PayType
) T
GROUP BY CreateTime

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

Code[http://www.oeedu.com]

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 ) TSET @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 ) TSET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2)   ' FROM ('   CHAR(10);SET @cmdText = @tmpSql   @cmdText   ') T GROUP BY CreateTime ';PRINT @cmdTextEXECUTE (@cmdText);

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

Code[http://www.oeedu.com]

SELECT         CreateTime, [支付宝] , [手机短信],        [工商银行卡] , [建设银行卡]FROM(    SELECT CONVERT(VARCHAR(10), CreateTime, 120) AS CreateTime,PayType, Money    FROM Inpours) PPIVOT (            SUM(Money)            FOR PayType IN            ([支付宝], [手机短信], [工商银行卡], [建设银行卡])      ) AS TORDER 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来实现。假如有下面这么一个表

代码

0 0