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语句