关于#sql#的问题：行列转换时，由于不知道要转换成列的字段有几种值

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 )
报错：不允许将非常量表达式用于pivot|unpivo

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

您可以使用动态 SQL 来解决这个问题，动态 SQL 允许您在运行时动态地构建 SQL 查询。在这种情况下，您需要动态地构建 'FOR subject IN:

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

-- 获取要转换的字段列表
SELECT @cols = COALESCE(@cols + ',', '') + QUOTENAME(subject)
FROM (SELECT DISTINCT subject FROM n881820_students_score WHERE stuid = 10002) s

-- 构建查询
SET @query = 'WITH a AS (
                SELECT stuid, subject, score
                FROM n881820_students_score
                WHERE stuid = 10002
              )
              SELECT *
              FROM a
              PIVOT (
                SUM(score) FOR subject IN (' + @cols + ')
              ) AS p
              ORDER BY stuid'

-- 执行查询
EXECUTE(@query)

在上述代码中，我们使用 'SELECT DISTINCT

请注意，使用动态 SQL 时需要小心 SQL 注入攻击。在本例中，由于我们没有从外部参数中动态构建查询，因此不太可能受到注入攻击。但是，在其他情况下，您可能需要使用参数化查询来避免注入攻击。

解答二：
在Pivot中，IN子句需要指定常量值，而不能使用表达式或子查询。因此，您可以尝试使用动态SQL生成所需的列名，然后在Pivot中使用该列名列表。例如，可以使用以下查询：

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @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(score) for subject in (' + @cols + ')
) as p order by stuid'

exec sp_executesql @query

这将动态生成列名列表，并在Pivot中使用它们。