SQL Server如何根据列值动态设置列名？（值未知/数量多场景）

你的原查询无法得到预期结果，原因是仅用一个CASE语句将所有匹配col1 like 'nam%'的col2值聚合到了同一列，没有针对每个col1的唯一值生成独立的列分支。

一、已知col1取值的静态写法（参考） #

如果col1的可能值固定（比如name、name2、name3），可以直接写静态查询得到预期结果：

SELECT
  id,
  MAX(CASE WHEN col1 = 'name' THEN col2 ELSE NULL END) AS [name],
  MAX(CASE WHEN col1 = 'name2' THEN col2 ELSE NULL END) AS [name2],
  MAX(CASE WHEN col1 = 'name3' THEN col2 ELSE NULL END) AS [name3]
FROM yourtable
GROUP BY id
ORDER BY id

二、未知/大量col1取值的动态SQL写法 #

当col1的取值未知或数量过多时，必须通过动态SQL自动生成对应列的处理逻辑，以下是完整实现：

SQL Server 2017+版本（支持STRING_AGG） #

DECLARE @query NVARCHAR(MAX);

-- 拼接动态列的CASE语句片段
SET @query = N'
SELECT
  id,
  ' + (
    SELECT STRING_AGG(
      'MAX(CASE WHEN col1 = ' + QUOTENAME(col1, '''') + ' THEN col2 ELSE NULL END) AS ' + QUOTENAME(col1),
      ', ' + CHAR(10) + '  '
    ) WITHIN GROUP (ORDER BY col1)
    FROM (SELECT DISTINCT col1 FROM yourtable WHERE col1 LIKE ''nam%'') AS unique_cols
  ) + '
FROM yourtable
WHERE col1 LIKE ''nam%''
GROUP BY id
ORDER BY id;
';

-- 执行动态SQL
EXEC sp_executesql @query;

SQL Server 2016及更早版本（兼容写法） #

如果不支持STRING_AGG，用FOR XML PATH替代拼接逻辑：

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

-- 生成列处理片段
SELECT @cols = STUFF((
    SELECT ', ' + CHAR(10) + '  MAX(CASE WHEN col1 = ' + QUOTENAME(col1, '''') + ' THEN col2 ELSE NULL END) AS ' + QUOTENAME(col1)
    FROM (SELECT DISTINCT col1 FROM yourtable WHERE col1 LIKE 'nam%') AS unique_cols
    ORDER BY col1
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 拼接完整查询
SET @query = N'
SELECT
  id,
  ' + @cols + '
FROM yourtable
WHERE col1 LIKE ''nam%''
GROUP BY id
ORDER BY id;
';

-- 执行动态SQL
EXEC sp_executesql @query;

关键说明 #

• QUOTENAME函数：处理col1中包含特殊字符（空格、符号等）的情况，避免语法错误，同时防范SQL注入风险。
• 过滤条件WHERE col1 LIKE 'nam%'：保留你原查询的筛选逻辑，仅处理符合规则的col1值。

内容的提问来源于stack exchange，提问作者Hima Moore