行转列示例

IF object_id('tb') IS NOT NULL

DROP TABLE tb

go

CREATE TABLE tb(

 [姓名] varchar(10),

 [课程] varchar(10),

 [分数] INT

)

insert INTO tb values('张三','语文',74);

INSERT INTO tb values('张三','数学',83);

insert INTO tb values('张三','物理',93);

INSERT INTO tb values('李四','语文',74);

INSERT INTO tb values('李四','数学',84);

INSERT INTO tb values('李四','物理',94);

go

SELECT * FROM tb

go

DECLARE @sql varchar(500);

SET @sql='select 姓名'

SELECT @sql=@sql+',max(case 课程 when '''+课程+''' then 分数 else 0 end)['+课程+']'

FROM (SELECT DISTINCT 课程 FROM tb) a --同from tb group by课程，默认按课程名排序

SET @sql=@sql+' from tb group by 姓名'

exec(@sql);

--PRINT @sql;打印构造SQL语句