行列转换时，pivot sql 里的 in（xxx）参数能否用sql代替，报错：不允许将非常量表达式用于pivot|unpivot？

Question

行列转换时，由于不知道要转换成列的字段有几种值，sql怎么写?
with a as (select stuid,subject,score from n881820_students_score where stuid = 10002)
select * from a
pivot (
sum(a.score) FOR subject IN ('语文','数学','英语')
)
ORDER BY stuid;

把IN ('语文','数学','英语') 换成IN (select distinct subject from n881820_students_score where stuid = 10002 )

with a as (select stuid,subject,score from n881820_students_score where stuid = 10002),
b as (select distinct subject from n881820_students_score where stuid = 10002)
select * from a
pivot (
sum(a.score) FOR subject IN (b.subject)
)
ORDER BY stuid;

请问下该怎么写？

Answer 1

没有数据没有经过测试，但就这个意思，in中的内容通过字符串拼接出来，然后在拼接完SQL，最后执行，你自己调试一下

DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)


select @cols =STUFF((SELECT DISTINCT ','+ QUOTENAME([subject]) from n881820_students_score WHERE stuid = 10002 FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
    
set @query ='with a as (select stuid,subject,score from n881820_students_score where stuid = 10002)
select * from a
pivot (
sum(a.score) FOR subject IN (' + @cols + ')
)
ORDER BY stuid;'

execute(@query)