如何对两列应用PIVOT转单行？动态列场景实现方法

问题描述 #

我的源表包含两列，想要用PIVOT把它转成单行，但Name列是动态的，没法提前知道Name的数量和具体值。能不能不用指定'First Name'、'Last Name'、'Address1'这些值，让下面的查询正常运行？

原代码片段 #

Name in ('First Name', 'Last Name', 'Adddres1')

原查询语句 #

select * 
from
(
select 
n.Name, v.[Value]
from tblValues v
join tblNames n
ON v.NameID = n.NameID
where v.id = 123456
) src
pivot
(Max(Value)
For
Name in ('First Name', 'Last Name', 'Adddres1')
) as pivot_table

解决方案 #

SQL的静态PIVOT语法必须明确指定要转换的列名，没法直接支持动态列。要实现需求，得用动态SQL自动拼接列列表，具体实现如下：

动态SQL实现代码（SQL Server 2017+） #

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

-- 提取所有唯一Name值，拼接成符合语法的列列表（用方括号处理空格/特殊字符）
SELECT @cols = STRING_AGG(QUOTENAME(Name), ', ')
FROM (SELECT DISTINCT n.Name FROM tblValues v JOIN tblNames n ON v.NameID = n.NameID WHERE v.id = 123456) AS unique_names;

-- 拼接完整的PIVOT查询语句
SET @query = N'
SELECT * 
FROM (
    SELECT n.Name, v.[Value]
    FROM tblValues v
    JOIN tblNames n ON v.NameID = n.NameID
    WHERE v.id = 123456
) src
PIVOT (
    MAX([Value])
    FOR Name IN (' + @cols + N')
) AS pivot_table;';

-- 执行动态生成的查询
EXEC sp_executesql @query;

兼容旧版本SQL Server（2016及以下） #

如果你的SQL Server版本不支持STRING_AGG，可以用FOR XML PATH方式拼接列列表：

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

-- 用FOR XML PATH拼接列名
SELECT @cols = STUFF((SELECT ', ' + QUOTENAME(Name)
                      FROM (SELECT DISTINCT n.Name FROM tblValues v JOIN tblNames n ON v.NameID = n.NameID WHERE v.id = 123456) AS unique_names
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

-- 拼接并执行查询
SET @query = N'
SELECT * 
FROM (
    SELECT n.Name, v.[Value]
    FROM tblValues v
    JOIN tblNames n ON v.NameID = n.NameID
    WHERE v.id = 123456
) src
PIVOT (
    MAX([Value])
    FOR Name IN (' + @cols + N')
) AS pivot_table;';

EXEC sp_executesql @query;

关键说明 #

• QUOTENAME函数会自动处理带空格或特殊字符的列名，避免SQL语法错误；
• 动态SQL会根据当前数据中的实际Name值自动生成对应列，无需提前硬编码；
• 若Name值来自不可信输入，需要额外做安全校验，防止SQL注入风险。

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