sql server 交叉表 行转列 显示

[color=red]三个表（要求把学生每次考试情况列出来）[/color]
[color=red]CREATE TABLE [dbo].[score](
[stuid] [int] NOT NULL,
[sid] [int] NOT NULL,
[score] [int] NOT NULL,
[examtimes] [int] NULL
)[/color]
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 )


[color=red] CREATE TABLE [Student] ( [studi] [int] NOT NULL , [stuname] [nvarchar] (50) NOT NULL )[/color]

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


[color=red] CREATE TABLE [Subject] ( [sid] [int] NOT NULL , [sname] [varchar] (50) NOT NULL )[/color]

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 --运行存储过程

[color=red]如果是固定列，就不用存储过程[/color]
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


[img]http://dl.iteye.com/upload/attachment/247491/1fd52764-a7f6-3717-8725-555277f28275.jpg[/img]