要完成如下功能地sql怎么样完成？

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



/*

??：普通行列??(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

declare @sql varchar(8000)

set @sql='select col1'

select @sql=@sql+',['+rtrim(col2)+']=max(case col2 when '+rtrim(col2)+' then col3 end)' from (select distinct col2 from tabname) t

set @sql=@sql+' from tabname group by col1 order by col1'

ex
[1] [2] 下一页