=波波日志 > SQL及数据库 > PIVOT/UNPIVOT的用法=
[转]PIVOT/UNPIVOT的用法
SQL Server2005引入了很多迎合开发者口味的新特性，虽然改动不大，却大大了减少了开发者的工作量，这种替用户考虑的开发思路，值得称赞。
在SQL Server2000中，要实现行列转换，需要综合利用聚合函数和动态SQL，实现起来需要一定的技巧，所以在CSDN的SQL讨论区里可以看到大量询问行列转换如何实现的问题。到了2005中，使用新引进的关键字PIVOT/UNPIVOT，可以轻松实现行列转换的需求。
好像Oracle11g也准备引入PIVOT/UNPIVOT特性，对于Oracle开发来说，It''s a good news。
本文通过两个简单的例子展示PIVOT/UNPIVOT的用法。详细的语法请参考联机帮助。
PIVOT
创建测试表，插入测试数据
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
(8 row(s) affected)
利用PIVOT将个季度的利润转成横向显示：
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
UNPIVOT
建立测试表，插入测试数据
id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
利用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：
id name quarter profile
----------- -------------------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500
(8 row(s) affected)
来源：http://2sws.blog.163.com/blog/static/1791024920098434542875/
在SQL Server2000中，要实现行列转换，需要综合利用聚合函数和动态SQL，实现起来需要一定的技巧，所以在CSDN的SQL讨论区里可以看到大量询问行列转换如何实现的问题。到了2005中，使用新引进的关键字PIVOT/UNPIVOT，可以轻松实现行列转换的需求。
好像Oracle11g也准备引入PIVOT/UNPIVOT特性，对于Oracle开发来说，It''s a good news。
本文通过两个简单的例子展示PIVOT/UNPIVOT的用法。详细的语法请参考联机帮助。
PIVOT
创建测试表，插入测试数据
+展开
-SQL
create table test(id int,name varchar(20),quarter int,profile int)
insert into test values(1,''a'',1,1000)
insert into test values(1,''a'',2,2000)
insert into test values(1,''a'',3,4000)
insert into test values(1,''a'',4,5000)
insert into test values(2,''b'',1,3000)
insert into test values(2,''b'',2,3500)
insert into test values(2,''b'',3,4200)
insert into test values(2,''b'',4,5500)
select * from test
id name quarter profile
insert into test values(1,''a'',1,1000)
insert into test values(1,''a'',2,2000)
insert into test values(1,''a'',3,4000)
insert into test values(1,''a'',4,5000)
insert into test values(2,''b'',1,3000)
insert into test values(2,''b'',2,3500)
insert into test values(2,''b'',3,4200)
insert into test values(2,''b'',4,5500)
select * from test
id name quarter profile
----------- -------------------- ----------- -----------
1 a 1 1000
1 a 2 2000
1 a 3 4000
1 a 4 5000
2 b 1 3000
2 b 2 3500
2 b 3 4200
2 b 4 5500
(8 row(s) affected)
利用PIVOT将个季度的利润转成横向显示：
+展开
-SQL
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度"
from
test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4])
)
as pvt
id name 一季度 二季度 三季度 四季度
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
UNPIVOT
建立测试表，插入测试数据
+展开
-SQL
drop table test
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test values(1,''a'',1000,2000,4000,5000)
insert into test values(2,''b'',3000,3500,4200,5500)
select * from test
create table test(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into test values(1,''a'',1000,2000,4000,5000)
insert into test values(2,''b'',3000,3500,4200,5500)
select * from test
id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
1 a 1000 2000 4000 5000
2 b 3000 3500 4200 5500
(2 row(s) affected)
利用UNPIVOT，将同一行中四个季度的列数据转换成四行数据：
+展开
-SQL
select id,name,quarter,profile
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
from
test
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt
id name quarter profile
----------- -------------------- ---------- -----------
1 a Q1 1000
1 a Q2 2000
1 a Q3 4000
1 a Q4 5000
2 b Q1 3000
2 b Q2 3500
2 b Q3 4200
2 b Q4 5500
(8 row(s) affected)
来源：http://2sws.blog.163.com/blog/static/1791024920098434542875/
类别：SQL及数据库 作者：转载 日期：2010-12-09 【评论：0】
相关文章
暂时没有评论！
发表留言
热门博文
- access数据库导入mssqlserver保留自动增长列
- mssqlserver数据库导入access保留自动增长列
- 无法连接到WMI提供程序。你没有权限或者该服务器无法访问
- 链接Access出现“找不到可安装的 ISAM”错误的问题
- asp连接mssql“出现未找到提供程序。该程序可能未正确安装”错误
- Windows7如何安装SQL Server 2000
最新博文
- Sql Server参数化优化查询
- sqldataadapter update更新dataset无法同时更新多个表
- SqlServer2000无法打开用户默认数据库。登录失败
- sqlserver2005如何转移到sqlserver2000
- 'sa'用户登录失败。原因：未与信任 SQL Server 连接相关联
- SQL Server 2005 无法连接
随机博文
- SQLserver里面的加密函数
- 让服务器重启的死循环
- 使用微软分布式缓存服务Velocity Part 1
- windows xp sp3 安装Sql 2005失败处理
- SQL Server的备份还原功能之二：主要备份类型
- msSQL2005数据库备份
广告商赞助
