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

作者站长头像
站长
· 阅读数 6

行列转换时，由于不知道要转换成列的字段有几种值，sql怎么写?with a as (select stuid,subject,score from n881820_students_score where stuid = 10002)select * from apivot ( sum(a.score) FOR subject IN ('语文','数学','英语')) ORDER BY stuid;行列转换时，pivot sql 里的 in（xxx）参数能否用sql代替，报错：不允许将非常量表达式用于pivot|unpivot？把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 apivot ( sum(a.score) FOR subject IN (b.subject)) ORDER BY stuid;

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

回复
1个回答
avatar
test
2024-07-10

没有数据没有经过测试，但就这个意思，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)
回复
likes
适合作为回答的
  • 经过验证的有效解决办法
  • 自己的经验指引，对解决问题有帮助
  • 遵循 Markdown 语法排版，代码语义正确
不该作为回答的
  • 询问内容细节或回复楼层
  • 与题目无关的内容
  • “赞”“顶”“同问”“看手册”“解决了没”等毫无意义的内容