行转列之SQL SERVER PIVOT怎么用

114次阅读

共计 2997 个字符，预计需要花费 8 分钟才能阅读完成。

这篇文章主要介绍行转列之 SQL SERVER PIVOT 怎么用，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

在数据库操作中，有些时候我们遇到需要实现“行转列”的需求，例如一下的表为某店铺的一周收入情况表：

WEEK_INCOME(WEEK VARCHAR(10),INCOME DECIMAL)

我们先插入一些模拟数据：

INSERT INTO WEEK_INCOME 
SELECT  星期一 ,1000
UNION ALL
SELECT  星期二 ,2000
UNION ALL
SELECT  星期三 ,3000
UNION ALL
SELECT  星期四 ,4000
UNION ALL
SELECT  星期五 ,5000
UNION ALL
SELECT  星期六 ,6000
UNION ALL
SELECT  星期日 ,7000

一般我们最经常使用的查询是查询一周中每天或某几天的收入，例如查询周一至周日全部的收入：

SELECT WEEK,INCOME FROM WEEK_INCOME

得到如下的查询结果集：

WEEK INCOME
星期一 1000
星期二 2000
星期三 3000
星期四 4000
星期五 5000
星期六 6000
星期日 7000

但是在一些情况下（往往是某些报表中），我们希望在一行中显示周一至周日的收入，这时候查询结果集应该是这样的：

星期一星期二星期三星期四星期五星期六星期日
1000 2000 3000 4000 5000 6000 7000

这种情况下，SQL 查询语句可以这样写：

SELECT 
SUM(CASE WEEK WHEN  星期一  THEN INCOME END) AS [星期一],
SUM(CASE WEEK WHEN  星期二  THEN INCOME END) AS [星期二],
SUM(CASE WEEK WHEN  星期三  THEN INCOME END) AS [星期三],
SUM(CASE WEEK WHEN  星期四  THEN INCOME END) AS [星期四],
SUM(CASE WEEK WHEN  星期五  THEN INCOME END) AS [星期五],
SUM(CASE WEEK WHEN  星期六  THEN INCOME END) AS [星期六],
SUM(CASE WEEK WHEN  星期日  THEN INCOME END) AS [星期日]
FROM WEEK_INCOME

但是，在 SQL SERVER 2005 中提供了更为简便的方法，这就是 PIVOT 关系运算符。（相反的“列转行”是 UNPIVOT），以下是使用 PIVOT 实现“行转列”的 SQL 语句

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]
FROM WEEK_INCOME
PIVOT
 SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])
)TBL

请参考 MSDN 中关于 PIVOT 的用法：

http://technet.microsoft.com/zh-cn/library/ms177410(v=sql.105).aspx

但是 MSDN 上的描述太过于规范严肃，我看了半天还没弄清楚怎样使用 PIVOT，搞不清楚 PIVOT 里面的语法的含义。于是又 google 了很多资料，以及通过上面提到的 WEEK_INCOME 表例子作了试验，最终搞清楚了其用法。在网上有篇博文解释的很好：http://rely1020.blog.ithome.com.tw/post/1606/39111，基本上我要写的就是参照该博文，再加上自己一点个人理解。

要理解 PIVOT 语法，就是要清楚微软为什么这样设计 PIVOT，但我相信是现实需求催生设计思路，所以归根到底我们还是要弄清楚什么是“行转列”：

正常情况下的查询结果是这样：

星期一 1000
星期二 2000
星期三 3000
星期四 4000
星期五 5000
星期六 6000
星期日 7000

行转列后是这样：

星期一星期二星期三星期四星期五星期六星期日
1000 2000 3000 4000 5000 6000 7000

也就是说，行转列后，原来的某个列的值变做了列名，在这里就是原来 WEEK 列的值“星期一”, 星期二 … 星期日边做了列名，而我们需要做的另一个工作就是计算这些列的值（这里的“计算”其实就是 PIVOT 里面的聚合函数(sum,avg 等)）

现在结合注释来分析一下 PIVOT 语法(在这之前最好看看我上面提到博文：http://rely1020.blog.ithome.com.tw/post/1606/39111，里面说到的 PIVOT 语法的三个步骤挺重要)：

SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]-- 这里是 PIVOT 第三步（选择行转列后的结果集的列）这里可以用“*”表示选择所有列，也可以只选择某些列(也就是某些天)
FROM WEEK_INCOME -- 这里是 PIVOT 第二步骤 (准备原始的查询结果，因为 PIVOT 是对一个原始的查询结果集进行转换操作，所以先查询一个结果集出来) 这里可以是一个 select 子查询，但为子查询时候要指定别名，否则语法错误
PIVOT
 SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])-- 这里是 PIVOT 第一步骤，也是核心的地方，进行行转列操作。聚合函数 SUM 表示你需要怎样处理转换后的列的值，是总和 (sum)，还是平均(avg) 还是 min,max 等等。例如如果 week_income 表中有两条数据并且其 week 都是“星期一”，其中一条的 income 是 1000, 另一条 income 是 500，那么在这里使用 sum，行转列后“星期一”这个列的值当然是 1500 了。后面的 for [week] in([星期一],[星期二]...)中  for [week]就是说将 week 列的值分别转换成一个个列，也就是“以值变列”。但是需要转换成列的值有可能有很多，我们只想取其中几个值转换成列，那么怎样取呢？就是在 in 里面了，比如我此刻只想看工作日的收入，在 in 里面就只写“星期一”至“星期五”（注意，in 里面是原来 week 列的值, 以值变列）。总的来说，SUM(INCOME) for [week] in([星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日])这句的意思如果直译出来，就是说：将列 [week] 值为 星期一 , 星期二 , 星期三 , 星期四 , 星期五 , 星期六 , 星期日 分别转换成列，这些列的值取 income 的总和。)TBL-- 别名一定要写

以上是我对 PIVOT 的理解，我尽所能表达出来。不过话说回来，个人的理解的方式也不同，就如我开始看了很多篇博文，都没有搞清楚 PIVOT 用法。结果还是硬的通过例子和别人的博文再加上思考才弄懂了，所以如果各位看了本篇之后仍不能理解，那很正常，配合例子再加上自己思考，慢慢的定能理解。

以上是“行转列之 SQL SERVER PIVOT 怎么用”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注丸趣 TV 行业资讯频道！

正文完