sql server 交叉表 行转列 显示

三个表（要求把学生每次考试情况列出来）
CREATE TABLE [dbo].[score](
[stuid] [int] NOT NULL,
[sid] [int] NOT NULL,
[score] [int] NOT NULL,
[examtimes] [int] NULL
)
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050701 , 1001 , 10 , 1 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050701 , 1002 , 20 , 1 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050701 , 1003 , 30 , 1 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050702 , 1001 , 11 , 1 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050702 , 1002 , 21 , 1 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050702 , 1003 , 31 , 1 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050703 , 1001 , 12 , 1 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050703 , 1002 , 22 , 1 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050703 , 1003 , 32 , 1 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050701 , 1001 , 110 , 2 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050701 , 1002 , 120 , 2 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050701 , 1003 , 130 , 2 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050702 , 1001 , 111 , 2 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050702 , 1002 , 121 , 2 )
INSERT [score] ( [stuid] , [sid] , [score] , [examtimes] ) VALUES ( 2010050702 , 1003 , 131 , 2 )


CREATE TABLE [Student] (  [studi] [int] NOT NULL , [stuname] [nvarchar] (50) NOT NULL )

INSERT [Student] ( [studi] , [stuname] ) VALUES ( 2010050701 , '张三' )
INSERT [Student] ( [studi] , [stuname] ) VALUES ( 2010050702 , '李四' )
INSERT [Student] ( [studi] , [stuname] ) VALUES ( 2010050703 , '王五' )



CREATE TABLE [Subject] (  [sid] [int] NOT NULL , [sname] [varchar] (50) NOT NULL )

INSERT [Subject] ( [sid] , [sname] ) VALUES ( 1001 , '语文' )
INSERT [Subject] ( [sid] , [sname] ) VALUES ( 1002 , '数学' )
INSERT [Subject] ( [sid] , [sname] ) VALUES ( 1003 , '英语' )

create view view_stu as --三个表Student(学生信息表一),Score(多对多关联表，有成绩字段多),Subject(课程名表一)
select a.stuname,c.sname,b.score,b.examtimes from Student a left join score b on a.studi=b.stuid
inner join Subject c on b.sid=c.sid group by a.stuname,b.examtimes,c.sname,b.score

select * from view_stu --查询视图,得到一个过滤后能做列和分组行的表
--此存储过程可通用，只需把视图和变量更改就可以
CREATE procedure CorssTab
@strTabName as varchar(50) = 'view_stu', --此处放表名
@strCol as varchar(50) = 'sname',                       --表头分组依据字段
@strGroup as varchar(50) = 'stuname',--分组字段
@strNumber as varchar(50) = 'score',    --被统计的字段
@strSum as varchar(10) = 'Sum',     --运算方式
@examtimes as varchar(10) = 'examtimes' --第几次考试
AS

DECLARE @strSql as varchar(1000), @strTmpCol as varchar(100)
EXECUTE ('DECLARE corss_cursor CURSOR FOR SELECT DISTINCT ' + @strCol + ' from ' + @strTabName + ' for read only ') --生成游标
begin
  SET nocount ON
  SET @strsql ='select ' + @strGroup + ',' + @examtimes + ', ' + @strSum + '(' + @strNumber + ') AS [' + @strSum + ' of ' + @strNumber + ']' --查询的前半段

  OPEN corss_cursor
  while (0=0)
  BEGIN
    FETCH NEXT FROM corss_cursor --遍历游标，将列头信息放入变量@strTmpCol
    INTO @strTmpCol
    if (@@fetch_status<>0) break
          SET @strsql = @strsql + ', ' + @strSum + '(CASE ' + @strCol + ' WHEN ''' + @strTmpCol + ''' THEN ' + @strNumber + ' ELSE Null END) AS [' + @strTmpCol + ' ' + @strCol + ']' --构造查询
  END
        SET @strsql = @strsql + ' from ' + @strTabname + ' group by ' + @strGroup+','+ @examtimes --查询结尾

  EXECUTE(@strsql) --执行

  IF @@error <>0 RETURN @@error --如果出错，返回错误代码
  CLOSE corss_cursor
  DEALLOCATE corss_cursor RETURN 0 --释放游标，返回0表示成功

end
GO

EXEC CorssTab --运行存储过程

如果是固定列，就不用存储过程
select distinct t.name 姓名,t1.grade 语文,t2.grade 数学,t3.grade 英语
     from course t,
       (select c.name,c.grade from course c where c.subject='语文')t1,
       (select c.name,c.grade from course c where c.subject='数学')t2,
       (select c.name,c.grade from course c where c.subject='英语')t3
     where t.name=t1.name
        and t.name = t2.name
        and t.name = t3.name