关于 sql 行转列的感悟

标签：sql 数据库 感悟 行转列 原创作品，允许转载，转载时请务必以超链接形式标明文章 原始出处 、作者信息和本声明。否则将追究法律责任。http://mikoo.blog.51cto.com/627637/124516 自从看《学技术的不能自废武功》这篇文章后，我深有感触，于是对自己以前学过的知识又重新温习了一次，结果终于发现了一些新的感悟，现将其感悟发表，于大家共勉： 关于 sql 行转列的感悟 (表1) 要将其转变为： (表2) 可以看得出来，表2列是由表1的行数据转化而来的，只有姓名列是表1中的列。 一步一步的来嘛，其他的列难搞，如果表2只有1列姓名的话，那么very easy了，不就是一个简单的分组嘛。 Select 姓名 From 表1 Group By 姓名 现在sql语句的架子搭起来了，无论以后如何变化，分组是少不了的。 那么开始下一步，想这个结果集中再添加1列，多了我们不加，因为你不论是能处理语文，还是数学，还是英语列，那么其他的列只要原样照抄就可以了，顶多修改一下参数而已。 从语文列开始吧，这一列的数据都可以从表1中找到，如果我们只要“语文”这一列，那么也好写。 Sql语句疑似： Select 姓名,case when 科目='语文' then 成绩 end From 成绩表 Group By 姓名 那就F5吧。 嗯，啊，...挂了。 赶紧看错误提示： 服务器: 消息 8120，级别 16，状态 1，行 1 列 '成绩表.成绩' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。 服务器: 消息 8120，级别 16，状态 1，行 1 列 '成绩表.科目' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。 从错误信息中，可知字段成绩和科目还必须在聚合函数中或Group By中，如何在Group By中呢？ 数据倒是有了，可是行多了点，看来只能从聚合入手了。 到底是那个聚合？聚合有max,min,avg,sum,count，max和min首先干掉，我们不是要最大值，最小值，难道是平均值？难道是个数？难道是求和？？？ 那就试试呗。 Select 姓名,sum(case when 科目='语文' then 成绩 end) From 表1 Group By 姓名 嘿，真不错。 该有的都有了，就是没列名，好办，给个别名嘛。 Select 姓名,sum(case when 科目='语文' then 成绩 end) As '语文' From表1 Group By 姓名 那么其他几列也就好办了。 Select 姓名, sum(case when 科目='语文' then 成绩 end) As '语文' , sum(case when 科目='数学' then 成绩 end) As '数学', sum(case when 科目='英语' then 成绩 end) As '英语' From 表1 Group By 姓名 可是结果有NULL，那还不好办，你的case when then end没写完，还有else没写呢！ Select 姓名, sum(case when 科目='语文' then 成绩 else 0 end) As '语文' , sum(case when 科目='数学' then 成绩 else 0 end) As '数学', sum(case when 科目='英语' then 成绩 else 0 end) As '英语' From 表1 Group By 姓名 终于修成正果了。 真的？ 当然是假的？ 怎么假？ 这不废话么！你现在写的Sql语句只能支持科目表中只有语文数学英语，那万一课程中加了一个叫物理的，那你不得改程序了吗？！ 怎么办？凉拌。 也就是说这个Sql语句中有变化的地方在红字部分： Select 姓名, sum(case when 科目='语文' then 成绩 else 0 end) As '语文' , sum(case when 科目='数学' then 成绩 else 0 end) As '数学', sum(case when 科目='英语' then 成绩 else 0 end) As '英语' From 表1 Group By 姓名 红字部分要从哪里来？如果专心看，那么应该能从中间看出来，哪些个“As '语文'”，哪些个“科目='语文'”，其实是数据表中的值，如果仅仅要查询“语文，数学，英语”的话，那么easy啊。 Select 课程 From 表1 Group By 科目 可是我们要的结果是： sum(case when 科目='语文' then 成绩 else 0 end) As '语文' , sum(case when 科目='数学' then 成绩 else 0 end) As '数学', sum(case when 科目='英语' then 成绩 else 0 end) As '英语' …………….. 那不简单，就是些字符串嘛。 Select ',sum(case when 科目='''+trim(科目)+ ''' then 成绩 else 0 end) As '''+科目+'''' From 表1 Group By 课程 结果为： ,sum(case when 科目='语文' then 成绩 else 0 end) As '语文' , ,sum(case when 科目='数学' then 成绩 else 0 end) As '数学', ,sum(case when 科目='英语' then 成绩 else 0 end) As '英语' 哈哈，只要将这一段和刚才的Select 姓名拼在一起就可以了。 现在麻烦来了，拼没有问题。 declare @sql nvarchar(4000) set @sql='Select 姓名 ' Select @sql=@sql+',sum(case when 科目='''+科目+ ''' then 成绩 else 0 end) As '''+科目+'''' From表1 Group By 科目 Print @sql 这一段的结果是： Select 姓名 ,sum(case when 科目='数学' then 成绩 else 0 end) As '数学',sum(case when 科目='英语' then 成绩 else 0 end) As '英语',sum(case when 科目='语文' then 成绩 else 0 end) As '语文' 跟我们的要求比较接近了，还要附加一点东西： Select 姓名 ,sum(case when 科目='数学' then 成绩 else 0 end) As '数学',sum(case when 科目='英语' then 成绩 else 0 end) As '英语',sum(case when 科目='语文' then 成绩 else 0 end) As '语文' From 表1 Group By 姓名 现在麻烦的是，如何执行这个字符串，现在我们将sql语句放在了 @sql 这个变量中了，怎么执行？ 灵机1动！ SqlServer中有个专门将字符串作为sql语句执行的存储过程---- sp_executesql。 就是它了。 最终版本： declare @sql nvarchar(4000) set @sql='Select 姓名 ' Select @sql=@sql+',sum(case when 科目='''+科目+ ''' then 成绩 else 0 end) As '''+科目+'''' From 成绩表 Group By 科目 Print @sql set @sql=@sql+' From 成绩表 Group By 姓名' Print @sql execute sp_executesql @sql 当然科目信息，不一定会从成绩表中获取，很有可能是一个专门的表格，比如叫“科目表”，那么就更easy了。 declare @sql nvarchar(4000) set @sql='Select 姓名 ' Select @sql=@sql+',sum(case when 科目='''+科目+ ''' then 成绩 else 0 end) As '''+科目+'''' From 科目表 Print @sql set @sql=@sql+' From 表1 Group By 姓名' Print @sql execute sp_executesql @sql 功德圆满。 本文出自 “摩羯阿印” 博客，请务必保留此出处http://mikoo.blog.51cto.com/627637/124516

---

---

---

---

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

2010-07-28 08:40 by 潇湘隐者, 6293 visits,网摘,收藏,编辑

行转列，列转行是我们在开发过程中经常碰到的问题。行转列一般通过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 Subject WHEN ' 语文 ' THEN Score ELSE 0 END ) AS ' 语文 ' ,
MAX ( CASE Subject WHEN ' 数学 ' THEN Score ELSE 0 END ) AS ' 数学 ' ,
MAX ( CASE Subject WHEN ' 英语 ' THEN Score ELSE 0 END ) AS ' 英语 ' ,
MAX ( CASE Subject WHEN ' 生物 ' THEN Score ELSE 0 END ) AS ' 生物 '
FROM dbo. [ StudentScores ]
GROUP BY UserName

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

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

代码

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 , 1

INSERT INTO Inpours SELECT ' 张三 ' , ' 2010-06-14 ' , ' 支付宝 ' , 50 , 1

INSERT INTO Inpours SELECT ' 张三 ' , ' 2010-06-14 ' , ' 手机短信 ' , 100 , 1

INSERT INTO Inpours SELECT ' 李四 ' , ' 2010-06-14 ' , ' 手机短信 ' , 100 , 1

INSERT INTO Inpours SELECT ' 李四 ' , ' 2010-07-14 ' , ' 支付宝 ' , 100 , 1

INSERT INTO Inpours SELECT ' 王五 ' , ' 2010-07-14 ' , ' 工商银行卡 ' , 100 , 1

INSERT INTO Inpours SELECT ' 赵六 ' , ' 2010-07-14 ' , ' 建设银行卡 ' , 100 , 1

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

代码

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改写一下，我们就能轻松解决这个问题

代码

下面是通过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