Sql Server 2005 行转列的实现(横排)
SQL SERVER 2005中新增加了两个关系运算符 PIVOT/ UNPIVOT，能够实现表中的列转换到行，以及行到列的转换工作。举例，还是先创建测试数据表
Create TABLE sales.salesByMonth
[*](
[*]year char(4),
[*]month char(3),
[*]amount money,
[*]PRIMARY KEY (year, month)
[*])
[*]
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Jan', 789.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Feb', 389.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Mar', 8867.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Apr', 778.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','May', 78.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Jun', 9.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Jul', 987.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Aug', 866.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Sep', 7787.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Oct', 85576.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Nov', 855.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2004','Dec', 5878.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2005','Jan', 7.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2005','Feb', 6868.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2005','Mar', 688.0000)
[*]Insert INTO sales.salesByMonth (year, month, amount)
[*]VALUES('2005','Apr', 9897.0000)
我们想要得到类似这样的结果：
Year Jan Feb Mar …………..
—– ———- ———– ———–
2004 789.0000 389.0000 8867.0000 ………….
2005 7.0000 6868.0000 688.0000 …………..
[*]Select year,
[*]SUM(case when month = 'Jan' then amount else 0 end) AS 'Jan',
[*]SUM(case when month = 'Feb' then amount else 0 end) AS 'Feb',
[*]SUM(case when month = 'Mar' then amount else 0 end) AS 'Mar',
[*]SUM(case when month = 'Apr' then amount else 0 end) AS 'Apr',
[*]SUM(case when month = 'May' then amount else 0 end) AS 'May',
[*]SUM(case when month = 'Jun' then amount else 0 end) AS 'Jun',
[*]SUM(case when month = 'Jul' then amount else 0 end) AS 'Jul',
[*]SUM(case when month = 'Aug' then amount else 0 end) AS 'Aug',
[*]SUM(case when month = 'Sep' then amount else 0 end) AS 'Sep',
[*]SUM(case when month = 'Oct' then amount else 0 end) AS 'Oct',
[*]SUM(case when month = 'Nov' then amount else 0 end) AS 'Nov',
[*]SUM(case when month = 'Dec' then amount else 0 end) AS 'Dec'
[*]FROM sales.salesByMonth
[*]GROUP by year
但这样事实上还是相当麻烦的，现在SQLSERVER2005中有更方便的实现方法。
Select Year,,,,,,,,,,,,
[*]FROM (
[*]Select year, amount, month
[*]FROM sales.salesByMonth ) AS salesByMonth
[*]PIVOT ( SUM(amount) FOR month IN
[*] (,,,,,,,,,,,)
[*] ) AS ourPivot
[*]orDER BY Year
就是这样，很简单的用法，效果是完全一样的。
我们再尝试一下把year去掉：
Select ,,,,,,,,,,,
[*]FROM ( Select amount, month
[*]FROM sales.salesByMonth ) AS salesByMonth
[*]PIVOT ( SUM(amount) FOR month IN
[*](,,,,,,,,,,,)
[*]) AS ourPivot
得到的结果是：
Jan Feb Mar …
———- ———— ———–
796.0000 7257.0000 9555.0000 …
同一个月份的数据累加到一起。
再给个微软官方的例子：
页:
[1]