小记

--转贴
--作者：不详
--行转列+小计、总计
Create table test (name char(10),km char(10),cj int)
go
insert test values('张三','语文',80)
insert test values('张三','数学',86)
insert test values('张三','英语',75)
insert test values('李四','语文',78)
insert test values('李四','数学',85)
insert test values('李四','英语',77)

declare @sql varchar(8000)
set @sql = ' '

set @sql = @sql+' select aa.*,bb.小计 from '
set @sql = @sql+'( select name '
select @sql = @sql+ ',sum(case km when '''+km+''' then cj end) ['+km+']'
                 from (select distinct km from test) as a
set @sql = @sql+' from test  '
set @sql = @sql+'   group by name union all '

set @sql = @sql+'   select name=''总计'''
select @sql = @sql + ',sum(case km when '''+km+''' then cj end) ['+km+']'
                  from (select distinct km from test) as a
set @sql = @sql+' from test '

set @sql = @sql+' union all '
set @sql = @sql+' select name=''平均'' '
select @sql = @sql + ',convert(decimal(5,2),(sum(case km when '''+km+''' then cj end)*100/count(distinct test.name))/100.00) ['+km+']'
                  from (select distinct km from test) as a
set @sql = @sql+' from test '


set @sql = @sql+') aa left join ( '
set @sql = @sql+' select 小计=sum(cj),name from test group by name
                  union all
                  select 小计=sum(cj),name=''总计'' from test
                  union all
                  select 小计=convert(decimal(5,2),(sum(cj)*100/count(distinct test.name))/100.00),name=''平均'' from test )'
set @sql  = @sql+' bb on aa.name=bb.name '

exec(@sql)

drop table test