--使用stuff()
DECLARE @sql VARCHAR(8000)
SET @sql="" --初始化变量 @sql
SELECT @sql= @sql+"," + 课程 FROM tb GROUP BY 课程 --变量多值赋值
SET @sql= STUFF(@sql,1,1,"")--去掉首个","
SET @sql="select * from tb pivot (max(分数) for 课程 in ("+@sql+"))a"
PRINT @sql
exec(@sql)
--或使用isnull()
DECLARE @sql VARCHAR(8000)
--获得课程集合
SELECT @sql= ISNULL(@sql+",","")+课程 FROM tb
GROUP BY 课程
SET @sql="select * from tb pivot (max(分数) for 课程 in ("+@sql+"))a"
exec(@sql)