SQL 二维交叉报表 分类统计（行列转换）

/*

需要统计成以下形式 
            总    总分 数学   总分    语文       总分
李四    10    820    6    483.50    4        336.50
张三    10    829    7    595.50    3        233.50

ID Name   Class       Mark   Term
1 张三         数学         80.50 2001      
2 张三         数学         82.50 2002      
3 张三         数学         83.50 2003      
4 张三         数学         84.50 2004      
5 张三         数学         90.00 2005      
6 张三         数学         84.50 2006      
7 张三         数学         90.00 2007      
8 张三         语文         80.50 2001      
9 张三         语文         82.50 2002      
10 张三         语文         70.50 2003      
11 李四         数学         84.50 2000      
12 李四         数学         80.50 2001      
13 李四         数学         60.50 2002      
14 李四         数学         83.50 2003      
15 李四         数学         84.50 2004      
16 李四         数学         90.00 2005      
17 李四         语文         80.50 2001      
18 李四         语文         82.50 2002      
19 李四         语文         83.50 2003      
20 李四         语文         90.00 2005      

*/

CREATE TABLE temp(   
    [ID] [bigint] IDENTITY(1,1) NOT NULL,   
    [Name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,   
    [Class] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,   
    [Mark] [numeric](18, 2) NULL,   
    [Term] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,   
 CONSTRAINT [PK_temp] PRIMARY KEY CLUSTERED    
(   
    [ID] ASC  
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]   
) ON [PRIMARY]   
  
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','数学','80.5','2001')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','数学','82.5','2002')   
    INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','数学','83.5','2003')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','数学','84.5','2004')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','数学','90','2005')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','数学','84.5','2006')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','数学','90','2007')   
  
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','语文','80.5','2001')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','语文','82.5','2002')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('张三','语文','70.5','2003')   
  
  
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','数学','84.5','2000')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','数学','80.5','2001')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','数学','60.5','2002')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','数学','83.5','2003')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','数学','84.5','2004')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','数学','90','2005')   
  
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','语文','80.5','2001')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','语文','82.5','2002')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','语文','83.5','2003')   
INSERT INTO temp([Name],[Class],[Mark],[Term])VALUES('李四','语文','90','2005')   
  
DECLARE @sql varchar(2000)   
set @sql=''  
  
select @sql=@sql + ',class',count(id),sum(Mark) from temp  
group by Name,class   
  
  
declare @sql varchar(8000)   
set @sql = 'select name,count(name) as 总,sum(mark) as 总分'  
select @sql = @sql + ',count(case class when '''+class+''' then name end)['+class+'总数] ,sum(case class when '''+class+''' then mark end) ['+class+']'  
from (select distinct class from temp) as s   
  
select @sql = @sql+' from temp group by name'  
select @sql   
exec(@sql)   
  
--drop table temp