竪表轉橫表

------竪表轉橫表---------------------
----------------------分組統計-----------------------------
create table dbo.test(name varchar(50),nj varchar(50),km varchar(50),cj int)
insert into dbo.test(name,nj,km,cj) values('李四','一年級','英語',98)
insert into dbo.test(name,nj,km,cj) values('張三','一年級','語文',90)
insert into dbo.test(name,nj,km,cj) values('張三','一年級','數學',99)
insert into dbo.test(name,nj,km,cj) values('張三','一年級','英語',78)
insert into dbo.test(name,nj,km,cj) values('王五','一年級','語文',80)
insert into dbo.test(name,nj,km,cj) values('王五','一年級','數學',89)
insert into dbo.test(name,nj,km,cj) values('王五','一年級','英語',88)
insert into dbo.test(name,nj,km,cj) values('馬六','一年級','語文',70)
insert into dbo.test(name,nj,km,cj) values('馬六','一年級','數學',79)
insert into dbo.test(name,nj,km,cj) values('馬六','一年級','英語',88)
insert into dbo.test(name,nj,km,cj) values('王五','二年級','英語',89)
insert into dbo.test(name,nj,km,cj) values('王五','二年級','語文',81)
insert into dbo.test(name,nj,km,cj) values('王五','二年級','數學',90)
insert into dbo.test(name,nj,km,cj) values('李四','二年級','英語',99)
insert into dbo.test(name,nj,km,cj) values('馬六','二年級','英語',89)
insert into dbo.test(name,nj,km,cj) values('馬六','二年級','語文',71)
insert into dbo.test(name,nj,km,cj) values('馬六','二年級','數學',80)
insert into dbo.test(name,nj,km,cj) values('張三','二年級','英語',79)
insert into dbo.test(name,nj,km,cj) values('張三','二年級','語文',91)
insert into dbo.test(name,nj,km,cj) values('張三','二年級','數學',100)

declare @s varchar(8000)
select @s='select * from
(
select 
CASE WHEN (GROUPING(nj) = 1) and (grouping(name)<>1) THEN ''SUMnj''
  WHEN (GROUPING(nj) = 1) and (grouping(name) =1) THEN ''SUMALL''
ELSE ISNULL(nj, ''UNKNOWN'') END AS nj,
CASE WHEN (grouping(name)=1) and (GROUPING(nj) <> 1) THEN ''SUMkm''
  WHEN (grouping(name)=1) and (GROUPING(nj)  = 1) THEN ''SUMALL''
ELSE ISNULL(name, ''UNKNOWN'') END AS name'
select @s=@s+','+char(10)+'sum(case when nj='''+nj+''' and km='''+km+''' then cj else 0 end) as ['+nj+'_'+km+']'
from (select distinct nj,km from dbo.test) a
order by nj,km
select @s=@s+','+char(10)+'sum(case when nj='''+nj+''' then cj else 0 end) as ['+nj+']'
from (select distinct nj from dbo.test) a
order by nj
-- select @s=@s+','+char(10)+'sum(case when km='''+km+''' then cj else 0 end) as ['+km+']'
-- from (select distinct km from dbo.test) a
-- order by km
select @s=@s+char(10)+'from dbo.test group by nj,name with cube '
select @s=@s+char(10)+') aa'
select @s=@s+char(10)+'where nj<>''SUMnj'''
select @s=@s+char(10)+'order by name desc,nj'+char(10)
print @s
exec(@s)