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,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
- FROM (
- Select year, amount, month
- FROM sales.salesByMonth ) AS salesByMonth
- PIVOT ( SUM(amount) FOR month IN
- ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
- ) AS ourPivot
- orDER BY Year
就是这样,很简单的用法,效果是完全一样的。
我们再尝试一下把year去掉:
Select [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
- FROM ( Select amount, month
- FROM sales.salesByMonth ) AS salesByMonth
- PIVOT ( SUM(amount) FOR month IN
- ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
- ) AS ourPivot
得到的结果是:
Jan Feb Mar …
———- ———— ———–
796.0000 7257.0000 9555.0000 …
同一个月份的数据累加到一起。
再给个微软官方的例子:
分享到:
相关推荐
sqlserver 实现 行转列 split 分割的函数,具体使用方法写有在文件里。
实现由多行转化为多列显示,适合初级学者参考学习
sqlserver 动态行专列 避免了数据列过多的时候大量的使用case when then...... 原数据 : UserName Subject Score Nick 语文 80 Nick 数学 90 Nick 英语 70 Nick 生物 85 Kent 语文 80 Kent 数学 90 Kent ...
Sql Server里面的行转列,列转行
SqlServer如何进行行转列和列转行方法
行转列sql实例行转列sql实例行转列sql实例行转列sql实例行转列sql实例
sqlserver 行转成列 sqlserver 把行转成列 sqlserver 把行转成列
sqlserver 行数据转成列数据,#temp1为数据表,#temp2为辅助表,具体场景可微调替换。
SQL Server 2005 杂谈 在SQL Server2005中按列 连接字符串 三种方法 doc SQL Server 2005 杂谈 在SQL Server2005中按列 连接字符串 三种方法 doc
SQL行转列参考代码(sqlserver)
Microsoft SQL Server Management Studio Express(SSMSE)是一种免费、易于使用的图形管理工具,用于管理SQL Server 2005 Express Edition和SQL Server 2005 ExpressEdition with Advanced Services。注意:SSMSE ...
SQL 行转列+动态获取列名 通用的动态获取列名
Microsoft SQL Server 2005 向后兼容组件SQLServer2005_BC.msi
Microsoft SQL Server 2005 Backward Compatibility Components (Microsoft SQL Server 2005 向后兼容组件) SQL Server Backward Compatibility 包中包含 最新版本的 Data Transformation Services 2000 运行时 ...
列转行行转列
SQLServer2005_BC 32/64位 向后兼容兼容补丁
昨天遇到一个SQL Server的问题:需要写一个储存过程来处理几个表中的数据,最后问题出在我想将一个表的一个列的多行内容拼接成一行,比如表中有两列数据 : 类别 名称 AAA 企业1 AAA 企业2 AAA 企业3 ...
sql server 2005 向后兼容程序32位版 用于新版本程序兼容旧版本数据库
SQL Server2005基本方法和语句