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
-------------------
*/

create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go

--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名

--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')

---------------------------------

/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名语文数学物理平均分总分
---- ---- ---- ---- ------ ----
李四 74   84   94   84.00 252
张三 74   83   93   83.33 250
*/

--SQL SERVER 2000 静态SQL。
select 姓名姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名

--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql)

--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名

--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')

drop table tb

------------------
------------------

/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名语文数学物理
张三 74　　83　　93
李四 74　　84　　94
想变成(得到如下结果)：
姓名课程分数
---- ---- ----
李四语文 74
李四数学 84
李四物理 94
张三语文 74
张三数学 83
张三物理 93
--------------
*/

create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go

--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end

--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')

--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t

--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。

--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名课程   分数
---- ------ ------
李四语文   74.00
李四数学   84.00
李四物理   94.00
李四平均分 84.00
李四总分   252.00
张三语文   74.00
张三数学   83.00
张三物理   93.00
张三平均分 83.33
张三总分   250.00
------------------
*/

select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end

drop table tb

--/////////////////////////////

--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (项目 nvarchar(5),值 int,位置 int)
insert into #T
select '项目1',15,1 union all
select '项目1',34,2 union all
select '项目1',56,3 union all
select '项目1',42,4 union all
select '项目2',56,1 union all
select '项目2',67,2 union all
select '项目2',31,3 union all
select '项目2',89,4 union all
select '项目3',45,1 union all
select '项目3',22,2 union all
select '项目3',8,3 union all
select '项目3',23,4

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when 项目='''+项目+''' then 值 end) ['+项目+']'
from #T group by 项目

set @sql=stuff(@sql,1,1,'')

exec ('select '+@sql+',位置 from #T group by 位置')

/*
项目1         项目2         项目3         位置
----------- ----------- ----------- -----------
15          56          45          1
34          67          22          2
56          31          8           3
42          89          23          4
*/

--/////////////////////////////
表A
UnitCode   UnitName TargetCode TargetValue
23         北京公司     AAA        2222
24         上海公司     BBB        3333
25         深圳公司     CCC        4444
26         广州公司     DDD        5555
27         加拿大公司   EEE        6666
28         加拿大公司   AAA        1234
表B
TargetCodeName
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

--建立测试环境

set nocount on

create table tableA(UnitCode varchar(20),UnitName varchar(20),TargetCode varchar(20),TargetValue int)

insert into tableA select '23','北京公司','AAA','2222'

insert into tableA select '24','上海公司','BBB','3333'

insert into tableA select '25','深圳公司','CCC','4444'

insert into tableA select '26','广州公司','DDD','5555'

insert into tableA select '27','加拿大公司','EEE','6666'

insert into tableA select '28','加拿大公司','AAA','1234'

go

create table tableB(TargetCode varchar(20),Name varchar(20))

insert into tableB select 'AAA','始发收入'

insert into tableB select 'BBB','销售收入'

insert into tableB select 'CCC','其他收入'

insert into tableB select 'DDD','公里收入'

insert into tableB select 'EEE','货机收入'

go



declare @sql varchar(8000)

set @sql='select unitname'

select @sql=@sql+',sum(case when targetcode='''+targetcode+''' then targetvalue else 0 end)['+

Name+']' from tableB

set @sql=@sql+' from tableA group by unitname'



exec(@sql)

--删除测试环境

drop table tableB

drop table tableA

set nocount off



/*

unitname             始发收入        销售收入        其他收入        公里收入        货机收入

-------------------- ----------- ----------- ----------- ----------- -----------

北京公司                 2222        0           0           0           0

广州公司                 0           0           0           5555        0

加拿大公司                1234        0           0           0           6666

上海公司                 0           3333        0           0           0

深圳公司                 0           0           4444        0           0

*/