如何修改SQL实现动态透视时拼接"Name"列头与姓名字段？

现有Umpires表结构及数据 #

当前动态透视SQL查询 #

SET NoCount ON;

DECLARE @cols AS NVARCHAR(MAX), @sql AS NVARCHAR(MAX);

WITH GS AS(
SELECT LastN, Age, QUOTENAME(Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN)) As GrpSeq FROM Umpires),
DS AS
(SELECT DISTINCT GrpSeq FROM GS),
CS AS
(SELECT STRING_AGG(GrpSeq,',') AS G FROM DS)

SELECT @cols = G FROM CS

SELECT @sql = 'SELECT *
            FROM (SELECT LastN, Age, Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) As GrpSeq FROM Umpires) AS Q
            PIVOT(Min(LastN) FOR GrpSeq IN(' + @cols + ')) AS P;';

EXEC sp_executesql @sql;

当前查询输出 #

期望输出 #

需要实现列头拼接Name前缀，且透视列显示LastN, FirstN格式的完整姓名：

之前尝试遇到的问题 #

• 在内部SELECT中拼接字段并在PIVOT中引用时，触发错误：select statement that assigns a value to a variable must not be combined with data-retrieval operations
• 拼接Name字符串时触发语法错误：Incorrect syntax near 'Name'

可参考的其他实现方式 #

Access查询实现（大数据量性能差） #

TRANSFORM Max([LastN] & ", " & [FirstN]) AS FullName
SELECT Umpires.Age
FROM Umpires
GROUP BY Umpires.Age
PIVOT "Name" & DCount("*","Umpires","Age='" & [Age] & "' AND UmpID<=" & [UmpID]);

SQL Server静态透视实现 #

WITH DS AS(
SELECT Age, LastN + ', ' + FirstN AS FN, 'Name' + CAST(Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) AS VARCHAR) As GrpSeq FROM Umpires)

SELECT * FROM (SELECT Age, FN, GrpSeq FROM DS) AS Q PIVOT(Max(FN) FOR GrpSeq IN([Name1],[Name2],[Name3],[Name4])) P;

修改后的动态透视SQL #

SET NoCount ON;

DECLARE @cols AS NVARCHAR(MAX), @sql AS NVARCHAR(MAX);

-- 生成带Name前缀的透视列名，同时拼接完整姓名
WITH GS AS(
    SELECT 
        Age,
        LastN + ', ' + FirstN AS FullName,
        -- 生成带Name前缀的分组序号，并用QUOTENAME包裹避免语法问题
        QUOTENAME('Name' + CAST(Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) AS VARCHAR(10))) AS GrpSeq
    FROM Umpires
),
DS AS(
    SELECT DISTINCT GrpSeq FROM GS
),
CS AS(
    SELECT STRING_AGG(GrpSeq, ',') AS G FROM DS
)

SELECT @cols = G FROM CS;

-- 构建动态透视SQL，使用Max聚合FullName
SELECT @sql = '
SELECT *
FROM (
    SELECT 
        Age,
        LastN + '', '' + FirstN AS FullName,
        ''Name'' + CAST(Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) AS VARCHAR(10)) AS GrpSeq
    FROM Umpires
) AS Q
PIVOT(
    Max(FullName) FOR GrpSeq IN(' + @cols + ')
) AS P;';

EXEC sp_executesql @sql;

修改说明 #

1. 生成透视列名时添加Name前缀：在CTE的GrpSeq中直接拼接'Name'与序号，并用QUOTENAME包裹，确保列名符合SQL语法规范
2. 拼接完整姓名：在内部查询中生成LastN + ', ' + FirstN格式的完整姓名，作为透视的聚合字段
3. 聚合函数选择Max：因为每个分组序号对应唯一的姓名，Max和Min效果一致，但更符合语义
4. 修正变量赋值逻辑：确保变量赋值仅用于获取列名字符串，避免与数据查询操作混合，解决之前的错误

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