SQL SERVER PIVOT与用法解释
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 '星期四',4000UNION 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实现“行转列”
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表例子作了试验，最终稿清楚了其用法。在网上有篇博文解释的很好：T-SQL PIVOT姙法剖析
CREATE TABLE [StudentScores]
(
[UserName] NVARCHAR(20), --学生姓名
[Subject] NVARCHAR(30), --科目
[Score] FLOAT, --成绩
)
INSERT INTO [StudentScores] SELECT '张三', '语文', 80
INSERT INTO [StudentScores] SELECT '张三', '数学', 90
INSERT INTO [StudentScores] SELECT '张三', '英语', 70
INSERT INTO [StudentScores] SELECT '张三', '生物', 85
INSERT INTO [StudentScores] SELECT '李四', '语文', 80
INSERT INTO [StudentScores] SELECT '李四', '数学', 92
INSERT INTO [StudentScores] SELECT '李四', '英语', 76
INSERT INTO [StudentScores] SELECT '李四', '生物', 88
INSERT INTO [StudentScores] SELECT '码农', '语文', 60
INSERT INTO [StudentScores] SELECT '码农', '数学', 82
INSERT INTO [StudentScores] SELECT '码农', '英语', 96
INSERT INTO [StudentScores] SELECT '码农', '生物', 78
select * from StudentScores
--
---行转列
select * from [StudentScores]
AS P
pivot
(
sum(Score) FOR
p.Subject IN ([语文],[数学],[英语],[生物])
) AS T
----
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
select * from ProgrectDetail
---列转行
select P.ProgrectName,P.Supplier,P.SupplyNum
from
(
select ProgrectName, OverseaSupply, NativeSupply,
SouthSupply, NorthSupply
from ProgrectDetail
)T
unpivot
(
SupplyNum FOR Supplier IN
(OverseaSupply, NativeSupply, SouthSupply, NorthSupply )
) P
