--使用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)
