行转列SQLSERVERPIVOT与用法解释.docVIP

在数据库操作中，有些时候我们遇到需要实现“行转列”的需求，例如一下的表为某店铺的一周收入情况表： 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，搞不清楚PIVOT里面的语法的含义。于是又google了很多资料，以及通过上面提到的WEEK_INCOME 要理解PIVOT语法，就是要清楚微软为什么这样设计PIVOT，但我相信是现实需求催生设计思路，所以归根到底我们还是要弄清楚什么是“行转列”： 正常情况下的查询结果是这样： 星期一 1000星期二 2000星期三 3000星期四 4000星期五 5000星期六 6000星期日 7000 行转列后是这样： 星期一???星期二???星期三???星期四???星期五???星期六???星期日1000????2000????3000????4000????5000????6000????7000 也就是说，行转列后，原来的某个列的值变做了列名，在这里就是原来WEEK列的值“星期一”,星期二...星期日边做了列名，而我们需要做的另一个工作就是计算这些列的值（这里的“计算”其实就是PIVOT里面的聚合函数 sum,avg等 ） SELECT [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日]--这里是PIVOT第三步（选择行转列后的结果集的列）这里可以用“*”表示选择所有列，也可以只选择某些列 也就是某些天 FROM WEEK_INCOME --这里是PIVOT第二步骤 准备原始的查询结果，因为PIVOT是对一个原始的查询结果集进行转换操作，所以先查询一个结果集出来 这里可以是一个select子查询，但为子查询时候要指定别名，否则语法错误 PIVOT SUM INCOME for [week] in [星期一],[星期二],[星期三],[星期四],[星期五],[星期六],[星期日] --这里是PIVOT第