[转]SQL—-行列转换。经典SQL—-行列转换。

[转]SQL—-行列转换。经典SQL—-行列转换。

正文转自:http://www.cxy.me/doc/4885.htm

所属种类:SQL SERVER
章作者:jodie
引进指数:★★★
文档人气:3527
本周人气:7
宣布日期:2008-8-29

/*

 

题目:普通行列转换(version 2.0)

/*
题目:普通行列转换(version 2.0)
证实:普通行列转换(version 1.0)仅针对sql server
2000提供静态和动态写法,version 2.0多sql server 2005之有关写法。

说明:普通行列转换(version 1.0)仅针对sql server
2000提供静态和动态写法,version 2.0增sql server 2005之有关写法。

题材:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
纪念成为(得到如下结果): 
姓名 语文 数学 物理 

 


问题:假设有张学生成绩表(tb)如下:

李四 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

张三 语文 74

张三 物理 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

张三 数学 83

张三 总分   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

–> 生成测试数据: #DB_info
if object_id(‘tempdb.dbo.#DB_info’) is not null drop table
#DB_info
create table #DB_info (sid int,name nvarchar(4),sex nvarchar(2))
insert into #DB_info
select 1,’李明’,’男’ union all
select 2,’王军’,’男’ union all
select 3,’李敏’,’女’

–> 生成测试数据: #db_scores
if object_id(‘tempdb.dbo.#db_scores’) is not null drop table
#db_scores
create table #db_scores (sid int,type nvarchar(4),scores int)
insert into #db_scores
select 1,’语文’,80 union all
select 1,’数学’,90 union all
select 2,’语文’,85 union all
select 2,’数学’,90 union all
select 3,’语文’,75 union all
select 3,’数学’,85

declare @sql nvarchar(4000)
set @sql=’select a.sid,a.name,a.sex’
select @sql=@sql+’,max(case when b.type=”’+type+”’ then b.scores else
0 end) [‘+type+’]’
from (select distinct type from #db_scores) t

exec (@sql+’ from #DB_info a left outer join #db_scores b on
a.sid=b.sid group by a.sid,a.name,a.sex’)

/*
sid         name sex  数学          语文


1           李明   男    90          80
2           王军   男    90          85
3           李敏   女    85          75

(3 行让影响)
*/

原稿网址:http://www.programbbs.com/doc/4885.htm

张三 物理 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

 

–> 生成测试数据: #DB_info

if object_id(‘tempdb.dbo.#DB_info’) is not null drop table #DB_info

create table #DB_info (sid int,name nvarchar(4),sex nvarchar(2))

insert into #DB_info

select 1,’李明’,’男’ union all

select 2,’王军’,’男’ union all

select 3,’李敏’,’女’

 

–> 生成测试数据: #db_scores

if object_id(‘tempdb.dbo.#db_scores’) is not null drop table
#db_scores

create table #db_scores (sid int,type nvarchar(4),scores int)

insert into #db_scores

select 1,’语文’,80 union all

select 1,’数学’,90 union all

select 2,’语文’,85 union all

select 2,’数学’,90 union all

select 3,’语文’,75 union all

select 3,’数学’,85

 

declare @sql nvarchar(4000)

set @sql=’select a.sid,a.name,a.sex’

select @sql=@sql+’,max(case when b.type=”’+type+”’ then b.scores else
0 end) [‘+type+’]’

from (select distinct type from #db_scores) t

 

exec (@sql+’ from #DB_info a left outer join #db_scores b on
a.sid=b.sid group by a.sid,a.name,a.sex’)

 

/*

sid         name sex  数学          语文


1           李明   男    90          80

2           王军   男    90          85

3           李敏   女    85          75

 

(3 行让影响)

*/

 

admin

网站地图xml地图