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

行列转换时，由于不知道要转换成列的字段有几种值，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;

效果如下：

img

把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;

img

您可以使用动态 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中使用它们。

以下情况推荐使用动态转换：
1、需要转置的列不确定，比如转置月的天数，月份的天数有28.29.30.31天，不确定
2、需要转置的列非常多时候，不方便一个个写
详情参考这个博文的方法，链接：https://www.cnblogs.com/Bokeyan/p/14447537.html

PIVOT 子句需要指定要进行列转行的列和新表的列名，这里的列名必须是固定的。如果不知道要转换的列有哪些值，可以先进行查询并动态生成列名，然后再构造 PIVOT 查询。

https://www.baidu.com/link?url=zVWFC6W5QkrXu1xYIIO9JcAozbFBytnO60WUDgkBENUamCqy7ZbbXw3gRjy5Q4aU56EFQr7Tx_-9ioH5ZAPRrHIYrH563H8gARl2li4WBA7&wd=&eqid=d95c003d00028aa70000000263ec8f06

字段都存在一张表里面解决

PIVOT需要指定列值的名称，所以不能使用变量或子查询作为PIVOT子句中的列值。不过可以使用动态SQL来动态生成列值，从而实现行列转换。

在SQL中，PIVOT需要明确指定要转置的列，因此无法使用变量或子查询来替代。如果您不知道有哪些特定的值，可以先使用动态SQL生成查询语句，然后再执行查询。

以下是一个使用动态SQL的示例，可以将查询的结果动态地转置为所有不同的科目：

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

-- 获取所有不同的科目，将其连接为逗号分隔的字符串
SELECT @cols = STUFF(
(SELECT DISTINCT ',[' + 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'

-- 执行查询
EXECUTE(@query)

请注意，动态SQL可能存在SQL注入的风险，需要谨慎使用。