SQL Server中动态列数的实现：如何将指定Name-Id行数据转换为动态列结构？

没问题！这种把行数据转成动态列的需求在SQL Server里完全可以实现，核心思路是结合动态SQL和窗口函数——毕竟列数要跟着源数据里唯一的Name数量走，静态SQL肯定搞不定可变列的场景。下面我一步步给你拆解实现过程：

1. 先准备示例数据 #

首先咱们先把测试数据放到临时表里方便操作：

CREATE TABLE #TempData (
    Name VARCHAR(50),
    Id INT
);

INSERT INTO #TempData (Name, Id)
VALUES 
('Name1', 1),
('Name1', 2),
('Name2', 3),
('Name2', 4),
('Name2', 5),
('Name3', 6);

2. 给每个分组的行添加序号 #

要把同一Name下的多行数据对应到结果里的不同行，咱们需要给每个Name分组内的Id按顺序编个号，用ROW_NUMBER()窗口函数就能搞定：

SELECT 
    Name,
    Id,
    -- 按Name分组、Id排序，生成组内序号
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Id) AS RowNum
FROM #TempData;

这段查询会给每个Name下的Id标上1、2、3...的序号，这样后续就能把相同序号的行拼到结果的同一行里。

3. 构建动态SQL实现行转列 #

因为列数是动态的（取决于唯一Name的数量），咱们得先把所有唯一的Name拼接成列列表，再动态生成PIVOT语句：

DECLARE @Columns NVARCHAR(MAX);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 第一步：生成所有唯一的Name作为列名（SQL Server 2017+可用STRING_AGG）
SELECT @Columns = STRING_AGG(QUOTENAME(Name), ', ')
FROM (SELECT DISTINCT Name FROM #TempData) AS UniqueNames;

-- 第二步：构建动态PIVOT查询
SET @DynamicSQL = N'
SELECT ' + @Columns + '
FROM (
    SELECT 
        Name,
        Id,
        ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Id) AS RowNum
    FROM #TempData
) AS SourceData
PIVOT (
    -- 这里用MAX是因为每个RowNum+Name组合只有一个Id，MAX/MIN效果一致
    MAX(Id) FOR Name IN (' + @Columns + ')
) AS PivotTable;';

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

执行这段代码后，就能得到你想要的结果：

（注：如果某个Name的行数比其他少，对应的位置会显示NULL，你可以根据需求用ISNULL()替换成空字符串或其他值）

兼容SQL Server 2016及更早版本 #

如果你的SQL Server版本低于2017，STRING_AGG函数不可用，那就用STUFF+FOR XML PATH来拼接列列表：

-- 兼容旧版本的列拼接方式
SELECT @Columns = STUFF((
    SELECT ', ' + QUOTENAME(Name)
    FROM (SELECT DISTINCT Name FROM #TempData) AS UniqueNames
    ORDER BY Name
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');

关键要点总结 #

• 窗口函数的作用：ROW_NUMBER()给每个分组内的行编号，是实现“多行转多列”的基础，确保同一序号的行能对应到结果的同一行。
• 动态SQL的必要性：因为列数是可变的，必须动态生成列列表和PIVOT语句，静态SQL无法处理这种场景。
• QUOTENAME的重要性：用它包裹列名，能避免Name包含特殊字符（比如空格、中文）时出现语法错误，同时防止SQL注入风险。
• 聚合函数的选择：PIVOT必须配合聚合函数，这里用MAX是因为每个RowNum+Name组合只有一个Id，用MIN效果完全一样。

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