如何在现有SQL PIVOT查询中新增Jan Gp列（基于X.Gp）

要给现有的按月透视销售数据的查询加上「Jan Gp」列（对应数据源的X.Gp列），其实有两种简单的修改方式，我给你拆解一下：

方法一：直接在主查询中添加相关子查询（代码改动最小） #

这种方式不需要改动原有的PIVOT核心逻辑，只需要在最终的SELECT列表里新增一行，通过相关子查询直接提取每个客户1月的Gp总和：

select 
    pvtMonth.CardName,
    -- 新增的Jan Gp列：获取当前客户1月的Gp总和
    (SELECT SUM(Gp) FROM Data WHERE CardName = pvtMonth.CardName AND Month = 1) AS [Jan Gp],
    [1] as [Jan Sales], 
    [2] as [Feb Sales], 
    [3] as [Mar Sales], 
    [4] as [Apr Sales], 
    [5] as [May Sales], 
    [6] as [Jun Sales], 
    [7] as [Jul Sales], 
    [8] as [Aug Sales], 
    [9] as [Sep Sales], 
    [10] as [Oct Sales], 
    [11] as [Nov Sales], 
    [12] as [Dec Sales] 
from ( 
    select X.CardName, SUM(X.[Total Sales S$]) as [Sales $] , X.Month 
    from Data X 
    group by X.CardName ,X.Month 
) X 
PIVOT ( 
    sum(X.[Sales $]) 
    FOR [Month] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] ) 
) AS pvtMonth 
order by pvtMonth.CardName asc

• 优点：代码改动极小，逻辑直观，适合快速修改。
• 注意：如果你的Data表数据量很大，这种相关子查询可能会对性能有轻微影响（因为每个客户都会触发一次子查询）。

方法二：用窗口函数提前计算Jan Gp（性能更优） #

如果追求更好的性能，可以在PIVOT的源数据里提前计算好每个客户的Jan Gp，避免多次扫描表：

select 
    pvtMonth.CardName,
    [Jan Gp], -- 直接使用提前计算好的列
    [1] as [Jan Sales], 
    [2] as [Feb Sales], 
    [3] as [Mar Sales], 
    [4] as [Apr Sales], 
    [5] as [May Sales], 
    [6] as [Jun Sales], 
    [7] as [Jul Sales], 
    [8] as [Aug Sales], 
    [9] as [Sep Sales], 
    [10] as [Oct Sales], 
    [11] as [Nov Sales], 
    [12] as [Dec Sales] 
from ( 
    select 
        MonthlyData.CardName,
        MonthlyData.[Sales $],
        MonthlyData.Month,
        -- 用窗口函数计算每个客户1月的Gp总和，同一客户的所有行都会带上这个值
        SUM(CASE WHEN MonthlyData.Month = 1 THEN MonthlyData.[Monthly Gp] ELSE 0 END) 
            OVER (PARTITION BY MonthlyData.CardName) AS [Jan Gp]
    from (
        -- 先按客户+月份聚合销售和Gp数据
        select 
            X.CardName,
            SUM(X.[Total Sales S$]) as [Sales $],
            SUM(X.Gp) as [Monthly Gp],
            X.Month
        from Data X
        group by X.CardName, X.Month
    ) AS MonthlyData
) X 
PIVOT ( 
    sum(X.[Sales $]) 
    FOR [Month] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] ) 
) AS pvtMonth 
order by pvtMonth.CardName asc

• 优点：只需要扫描一次Data表，性能更出色，适合大数据量场景。
• 逻辑说明：先通过内层子查询按客户和月份聚合销售数据与当月Gp，再用窗口函数OVER (PARTITION BY CardName)计算每个客户1月的Gp总和，最后PIVOT透视时这个值会被保留到最终的每行数据中。

内容的提问来源于stack exchange，提问作者Vigna Hari Karthik