用SqlServer如何实现Decode函数固定列数的行列转换的功能?

lifeng40401 2008-09-25 02:45:05
student subject grade
--------- ---------- --------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
...全文
159 1 打赏 收藏 转发到动态 举报
写回复
用AI写文章
1 条回复
切换为时间正序
请发表友善的回复…
发表回复
yrwx001 2008-09-25
  • 打赏
  • 举报
回复
/*
??:普通行列??(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 ?程
set @sql = '[' + @sql + ']'
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


34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧