SQL Server中行列转换

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)

SELECT * FROM tb t

SELECT * FROM tb t
PIVOT (MAX(分数) for 姓名 in(张三,李四)) as aa

SELECT * FROM tb t
PIVOT (MAX(分数) for 课程 in(语文,数学,物理)) as aa

DECLARE @sql VARCHAR(MAX) SET @sql=''
SELECT @sql=@sql+','+t.姓名 FROM tb t
GROUP BY t.姓名 
SELECT @sql

set @sql='SELECT * FROM tb t PIVOT (MAX(分数) for 姓名 in('+stuff(@sql,1,1,'')+')) as aaj'
select (@sql)
EXEC (@sql)