sql server 行列转置【转】
参考博文：SQL Server行列转换[转]
第一次写随笔，有点生疏，借鉴了其他博友的文章作为学术参考，也许基本上都他人的东西，但是我觉得还是蛮实用的，因此在这里记录下这些我所学的东西，供大家参考。
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。
*/
/*
问题一：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
原博文采用静态和动态方法比较，方便理解。并且还指出 sql server 2000 和 sql server 2005 的不同之处。
创建表tb
1 --创建表tb
2 create table tb (姓名 varchar(10), 课程 varchar(10), 分数 int)
3 insert into tb values ('张三', '语文', 74)
4 insert into tb values ('张三', '数学', 83)
5 insert into tb values ('张三', '物理', 93)
6 insert into tb values ('李四', '语文', 74)
7 insert into tb values ('李四', '数学', 84)
8 insert into tb values ('李四', '物理', 94)
9 go
sql server 2000
1 --先说说之前在某公司用到过的方面，也是sql server 2000 运用到的方法
2 --静态方法:
3 select
4 姓名,
5 max(case 课程 when '语文' then 分数 else 0 end) 语文,
6 max(case 课程 when '数学' then 分数 else 0 end) 数学,
7 max(case 课程 when '物理' then 分数 else 0 end) 物理
8 from tb
9 group by 姓名
10 --动态方法：
11 declare @sql1 nvarchar(max)
12 select @sql1 = isnull(@sql1+',','') + ' max(case 课程 when '''+课程+''' then 分数 else 0 end) as ['+课程+']'
13 from (select distinct 课程 from tb) a
14 set @sql1 = 'select 姓名, '+@sql1+' from tb group by 姓名'
15 exec(@sql1)
sql server 2005，运用pivot()
1 --静态方法:
2 select *
3 from (select * from tb) a
4 pivot (max(分数) for 课程 in (语文,数学,物理)) b
5 --动态方法:
6 declare @sql2 nvarchar(max)
7 select @sql2 = isnull(@sql2+',','') + 课程 from tb group by 课程
8 exec('select *
9 from (select * from tb) a
10 pivot (max(分数) for 课程 in ('+@sql2+')) b ')
11 --运用stuff():删除指定长度的字符并在指定的起始点插入另一组字符。
12 declare @sql3 nvarchar(max) = ''
13 select @sql3 = @sql3+',' + 课程 from tb group by 课程
14 set @sql3 = stuff(@sql3, 1, 1, '') --删除@sql3从第一个字符开始的数1个字符，并插入空字符
15 exec('select *
16 from (select * from tb) a
17 pivot (max(分数) for 课程 in ('+@sql3+')) b ')
PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现。
PIVOT的一般语法是：
PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法：
table_source PIVOT( 聚合函数（value_column） FOR pivot_column IN(<column_list>) )
UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现。
完整语法：
table_source UNPIVOT( value_column FOR pivot_column IN(<column_list>) )
注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别 在数据库属性->选项->兼容级别改为 90
---------------------------------
/*
问题二：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
sql server 2000sql server 2005,运用pivot()------------------
/*
问题三：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/
创建表
1 create table tb2(姓名 varchar(10) , 语文 int , 数学 int , 物理 int) 2 insert into tb2 values('张三',74,83,93) 3 insert into tb2 values('李四',74,84,94) 4 go
sql server 2000
1 --静态方法： 2 select 姓名, '语文' 课程, 语文 3 from tb2 4 union all 5 select 姓名, '数学' 课程, 数学 6 from tb2 7 union all 8 select 姓名, '物理' 课程, 物理 9 from tb2 10 order by 姓名 11 --动态方法(注意引号内的空格)： 12 declare @sql31 nvarchar(max) 13 select @sql31 = isnull(@sql31 + ' union all ','') 14 + ' select 姓名, [课程] = '+quotename(name,'''') 15 + ', [分数] = '+quotename(name)+' from tb2 ' 16 from syscolumns 17 where ID = object_id('tb2') and name != N'姓名' --查找表名tb2，并且列名不是姓名的列 18 order by colid asc --根据列号排序 19 exec(@sql31 + 'order by 姓名 ')
sql server 2005,运用unpivot()
1 --静态方法: 2 select 姓名, 课程, 分数 3 from tb2 4 unpivot(分数 for 课程 in([语文],[数学],[物理]))b 5 order by 姓名 6 --动态方法: 7 declare @sql32 nvarchar(max) 8 select @sql32 = isnull(@sql32+',','') + quotename(name) 9 from syscolumns 10 where ID = object_id('tb2') and name != N'姓名' 11 exec('select 姓名, [课程], [分数] 12 from tb2 13 unpivot(分数 for 课程 in('+@sql32+') )b 14 order by 姓名') 15
--------------------
/*
问题四：上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/
sql server 2000
1 declare @sql41 nvarchar(max) 2 select @sql41 = isnull(@sql41+' union all ','') 3 + ' select 姓名, [课程] = '+quotename(name,'''')+', [分数] = '+quotename(name)+' from tb2' 4 from syscolumns 5 where ID = object_id('tb2') and name != N'姓名' 6 order by colid asc 7 exec('select * 8 from ('+ @sql41+') a 9 union all 10 select 姓名, ''平均分'', cast(avg([分数]*1.0) as numeric(38,2)) 11 from ('+ @sql41+') a 12 group by 姓名 13 union all 14 select 姓名, ''总分'', sum([分数]) 15 from ('+ @sql41+') a 16 group by 姓名 17 order by 姓名,[课程]')
/*
问题五:
项目 值 位置
'项目1',15,1
'项目1',34,2
'项目1',56,3
'项目1',42,4
'项目2',56,1
'项目2',67,2
'项目2',31,3
'项目2',89,4
'项目3',45,1
'项目3',22,2
'项目3', 8,3
'项目3',23,4
转换为:
项目1 项目2 项目3 位置
15 56 45 1
34 67 22 2
56 31 8 3
42 89 23 4
*/
创建表
1 create table T_Project (项目 nvarchar(5),值 int,位置 int) 2 insert into T_Project 3 select '项目1',15,1 union all 4 select '项目1',34,2 union all 5 select '项目1',56,3 union all 6 select '项目1',42,4 union all 7 select '项目2',56,1 union all 8 select '项目2',67,2 union all 9 select '项目2',31,3 union all 10 select '项目2',89,4 union all 11 select '项目3',45,1 union all 12 select '项目3',22,2 union all 13 select '项目3',8,3 union all 14 select '项目3',23,4
sql server 2000
1 --静态方法: 2 select 3 max(case when 项目='项目1' then 值 else 0 end) 项目1, 4 max(case when 项目='项目2' then 值 else 0 end) 项目2, 5 max(case when 项目='项目3' then 值 else 0 end) 项目3, 位置 6 from T_Project 7 group by 位置 8 --动态方法： 9 declare @sql51 nvarchar(max) 10 select @sql51 = isnull(@sql51+',','')+ 'max(case when 项目 = '''+项目+''' then 值 else 0 end) ['+项目+']' 11 from (select distinct 项目 from T_Project) a 12 exec ('select '+@sql51+', 位置 13 from T_Project 14 group by 位置 15 order by 位置' )
sql server 2005,运用pivot()
1 --静态方法： 2 select 项目1, 项目2, 项目3, 位置 from T_Project a 3 pivot(max(值) for 项目 in([项目1],[项目2],[项目3])) b 4 --动态方法： 5 declare @sql52 nvarchar(max) 6 select @sql52 = isnull(@sql52+',','')+项目 7 from T_Project 8 group by 项目 9 exec('select '+@sql52+', 位置 10 from T_Project a 11 pivot(max(值) for 项目 in('+@sql52+')) b')
/*问题六:
表A
UnitCode UnitName TargetCode TargetValue
23 北京公司 AAA 2222
24 上海公司 BBB 3333
25 深圳公司 CCC 4444
26 广州公司 DDD 5555
27 加拿大公司 EEE 6666
28 加拿大公司 AAA 1234
表B
TargetCode Name
AAA 始发收入
BBB 销售收入
CCC 其他收入
DDD 公里收入
EEE 货机收入
新出来的表后面的列根据B表Name来的
单位名称 始发收入 销售收入 其他收入 公里收入 货机收入
深圳公司 0 0 4444 0 0
北京公司 2222 0 0 0 0
上海公司 0 3333 0 0 0
广州公司 0 0 0 5555 0
加拿大公司 1234 0 0 0 6666
*/
创建表
1 set nocount on 2 create table tableA(UnitCode varchar(20),UnitName varchar(20),TargetCode varchar(20),TargetValue int) 3 insert into tableA select '23','北京公司','AAA','2222' 4 insert into tableA select '24','上海公司','BBB','3333' 5 insert into tableA select '25','深圳公司','CCC','4444' 6 insert into tableA select '26','广州公司','DDD','5555' 7 insert into tableA select '27','加拿大公司','EEE','6666' 8 insert into tableA select '28','加拿大公司','AAA','1234' 9 go 10 create table tableB(TargetCode varchar(20),Name varchar(20)) 11 insert into tableB select 'AAA','始发收入' 12 insert into tableB select 'BBB','销售收入' 13 insert into tableB select 'CCC','其他收入' 14 insert into tableB select 'DDD','公里收入' 15 insert into tableB select 'EEE','货机收入' 16 go
sql server 2000
1 declare @sql61 nvarchar(max) 2 select @sql61 = isnull(@sql61+',','') + 'max(case when b.Name = '''+Name+''' then a.TargetValue else 0 end) ['+Name+']' 3 from (select distinct Name from tableB) a 4 exec('select a.UnitName 单位名称, '+@sql61+' 5 from tableA a 6 left join tableB b on a.TargetCode = b.TargetCode 7 group by a.UnitName 8 order by min(a.UnitCode)')
sql server 2005,运用pivot()
1 declare @sql62 nvarchar(max) 2 select @sql62 = isnull(@sql62+',','') + Name 3 from tableB 4 order by TargetCode 5 exec('select 单位名称, '+@sql62+' 6 from (select a.UnitName as 单位名称, a.TargetCode, a.TargetValue, b.Name 7 from tableA a 8 left join tableB b on a.TargetCode = b.TargetCode) a 9 pivot(max(TargetValue) for Name in('+@sql62+')) c')
以上代码和实际结果有点偏差，后续会进行改进。
待续。。。
