SQL中如何正确创建动态Pivot函数实现分组金额行转列

你的代码存在两处核心逻辑错误，导致结果不符合预期：

• 透视列生成逻辑错误：直接将NetAmount的实际金额值作为列名，没有按照需求为同分组内的记录按顺序生成NetAmount、NetAmount2这类固定规则的序号列
• PIVOT聚合字段选择错误：聚合逻辑写的是max(TranID)，所以单元格返回的是交易ID值，而非需求要求的NetAmount金额值

核心实现思路：先通过窗口函数ROW_NUMBER()给每个Account+Bank Name分组内的记录按TranID升序编号，根据编号生成对应的透视列名（编号1对应NetAmount，编号2对应NetAmount2，以此类推），再执行动态透视，聚合时取NetAmount作为单元格值。

修正后的完整可运行代码如下：

SELECT  DISTINCT [Account]
        ,[Currency]
        ,TranID
        ,[Bank Code]
        ,[Bank Name]
        ,[Client No_]
        ,[NetAmount]
INTO #TEMP
FROM [My Table]

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

-- 生成动态透视列名
SELECT @cols = STUFF((
    SELECT ',' + QUOTENAME(COL_ALIAS)
    FROM (
        SELECT DISTINCT 
            CONCAT('NetAmount', 
                CASE WHEN ROW_NUMBER() OVER(PARTITION BY [Account], [Bank Name] ORDER BY TranID) = 1 
                    THEN '' 
                    ELSE CAST(ROW_NUMBER() OVER(PARTITION BY [Account], [Bank Name] ORDER BY TranID) AS VARCHAR(10)) 
                END) AS COL_ALIAS,
            ROW_NUMBER() OVER(PARTITION BY [Account], [Bank Name] ORDER BY TranID) AS rn
        FROM #TEMP
    ) t
    ORDER BY t.rn
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')

-- 拼接动态透视SQL
SET @query = '
SELECT [Account], [Bank Name], ' + @cols + ' 
FROM (
    SELECT  
        [Account],
        [Bank Name],
        NetAmount,
        CONCAT(''NetAmount'', 
            CASE WHEN ROW_NUMBER() OVER(PARTITION BY [Account], [Bank Name] ORDER BY TranID) = 1 
                THEN '''' 
                ELSE CAST(ROW_NUMBER() OVER(PARTITION BY [Account], [Bank Name] ORDER BY TranID) AS VARCHAR(10)) 
            END) AS COL_ALIAS
    FROM #TEMP
) x
PIVOT (
    MAX(NetAmount)
    FOR COL_ALIAS IN (' + @cols + ')
) p'

-- 执行动态语句
EXEC sp_executesql @query

• 代码中ROW_NUMBER()的ORDER BY TranID是控制同组金额横向排列顺序的规则，如果需要按交易时间、其他字段排序，直接替换排序字段即可
• 如果同组存在2条以上记录，代码会自动生成NetAmount3、NetAmount4……对应列，无需手动调整列名逻辑

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