SQL Server实现带行级总金额的Pivot行转列查询需求

原代码问题定位 #

• PIVOT转换后，客户无采购记录的品牌列默认返回NULL，未按要求填充为0
• 总金额计算逻辑错误：原代码将金额、数量、品牌字段混在同一粒度的子查询中，计算的是单条品牌交易的金额而非客户全品牌总采购额；且PIVOT过程未保留该字段，最终结果缺失要求的总金额列
• 隐性逻辑缺陷：PIVOT聚合采购数量时使用max(Qty)，若客户同一品牌存在多笔交易，会提取单条记录的最大数量而非品牌总采购量，不符合业务统计要求

修正后可运行代码 #

DECLARE 
    @colsPivot AS NVARCHAR(MAX), -- 用于PIVOT IN子句的品牌列清单
    @colsSelect AS NVARCHAR(MAX), -- 用于最终输出、空值转0的品牌列清单
    @query AS NVARCHAR(MAX)

-- 生成PIVOT匹配用的品牌列，严格按TABLE-A的PID排序保证列序正确
SELECT @colsPivot = STUFF(
    (SELECT ',' + QUOTENAME(BrandName) 
     FROM [TABLE-A] 
     ORDER BY PID 
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 1, ''
)

-- 生成最终输出用的品牌列，每个列包裹ISNULL实现空值转0
SELECT @colsSelect = STUFF(
    (SELECT ',' + 'ISNULL(' + QUOTENAME(BrandName) + ', 0) AS ' + QUOTENAME(BrandName)
     FROM [TABLE-A] 
     ORDER BY PID 
     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 1, ''
)

-- 拼装最终动态查询
SET @query = N'
WITH BrandQtySource AS (
    -- 客户-品牌粒度的采购数量，作为PIVOT转换源
    SELECT
        CustNo,
        [Name],
        BrandName,
        SUM(Qty) AS Qty
    FROM [TABLE-B]
    GROUP BY CustNo, [Name], BrandName
),
CustomerTotalAmt AS (
    -- 客户粒度的总采购金额，独立聚合避免粒度混淆
    SELECT
        CustNo,
        [Name],
        SUM(CAST(Amt AS float)) AS TotalAmt
    FROM [TABLE-B]
    GROUP BY CustNo, [Name]
)
SELECT
    p.CustNo,
    p.[Name],
    ' + @colsSelect + N',
    c.TotalAmt -- 结果末尾追加总金额列
FROM (
    SELECT CustNo, [Name], Qty, BrandName
    FROM BrandQtySource
) x
PIVOT (
    SUM(Qty) -- 聚合客户单品牌总采购量，替换原代码的max(Qty)
    FOR BrandName IN (' + @colsPivot + N')
) p
-- 关联匹配客户总金额
INNER JOIN CustomerTotalAmt c
    ON p.CustNo = c.CustNo AND p.[Name] = c.[Name]
ORDER BY p.CustNo;
'

-- 执行查询
EXEC sp_executesql @query;

方案匹配效果 #

• 所有品牌列无采购记录时统一填充为0，无NULL值残留
• 总金额字段为客户全渠道采购的累计值，固定追加在结果集末尾
• 品牌列顺序严格对齐TABLE-A的映射规则，不会出现列序错乱
• 单品牌采购数量为该客户同品牌所有交易的累计值，符合统计口径

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