如何为数据透视表添加自定义列？动态SQL实现问题求助

数据表结构与数据 #

Table1 #

Counterparty  Product  Deal  Date          Value
foo           bar      Buy    01/01/24     10.00
foo           bar      Buy    01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
foo           bar      Sell   01/01/24     10.00
fizz          bar      Buy    01/01/24     10.00
fizz          bar      Buy    01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00
fizz          buzz     Sell   01/01/24     10.00

Table2 #

Counterparty  Product  Deal  Date          Value
foo           bar      Buy    01/01/24     11.00
foo           bar      Buy    01/01/24     09.00
foo           bar      Sell   01/01/24     09.00
foo           bar      Sell   01/01/24     10.00
fizz          bar      Buy    01/01/24     12.00
fizz          bar      Buy    01/01/24     08.00
fizz          buzz     Sell   01/01/24     09.00
fizz          buzz     Sell   01/01/24     10.00

期望输出格式 #

Counterparty  Bar  Buzz  Total  col1 col2 col3 col4
foo           40    0      40    39    1   0    40 
fizz          20    20     40    39    1   0    40
Total         60    20     80    78    2   0    80

列规则定义 #

• col1：Table2中对应的Total值
• col2：Total与col1的差值
• col3：固定填充为0
• col4：Total与col3的差值

当前问题与现有代码 #

现有动态SQL仅能生成基础透视表：

Counterparty   Bar      Buzz
Fizz           20.00    20.00
Foo            40.00    0.00

无法添加自定义计算列并完成运算，现有代码如下：

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

-- Get Distinct Commodities
WITH cte AS (
    SELECT DISTINCT Commodity
    FROM Sample 
)
-- Generate dynamic columns from the table
SELECT @cols = COALESCE(@cols + ', ', '') + 'ISNULL(' + QUOTENAME(Commodity) + ', 0) AS ' + QUOTENAME(Commodity),
       @colsPivot = COALESCE(@colsPivot + ', ', '') + QUOTENAME(Commodity)
FROM cte
ORDER BY Commodity

-- add static columns to the pivot list:
SET @colsPivot = @colsPivot + ', [col1], [col2], [col3], [col4]'

-- Build the final query
SET @query = 
   'SELECT Counterparty, ' + @cols + ' 
    FROM (
        SELECT Counterparty, Commodity, SUM([Value]) AS TotalExposure
        FROM Sample
        GROUP BY Counterparty, Commodity
    ) AS pivotData
    PIVOT (
        SUM(TotalExposure)
        FOR Commodity IN (' + @colsPivot + ') --here lies the issue
    ) AS pivotTable'

PRINT(@query);

解决方案 #

问题根源 #

1. 错误地将静态计算列（col1-col4）加入PIVOT的FOR列表，这些列并非Commodity的取值，导致语法错误
2. 未关联Table1和Table2获取双方的合计值
3. 缺少总计行的汇总逻辑

完整实现代码 #

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

-- 获取所有唯一的Product
WITH cteProducts AS (
    SELECT DISTINCT Product FROM Table1
    UNION
    SELECT DISTINCT Product FROM Table2
)
-- 生成透视列和合计列表达式
SELECT 
    @cols = COALESCE(@cols + ', ', '') + 'ISNULL(' + QUOTENAME(Product) + ', 0) AS ' + QUOTENAME(Product),
    @colsSum = COALESCE(@colsSum + ' + ', '') + 'ISNULL(' + QUOTENAME(Product) + ', 0)'
FROM cteProducts
ORDER BY Product

-- 构建最终查询
SET @query = N'
WITH Table1Totals AS (
    -- 统计Table1分组合计
    SELECT 
        Counterparty,
        Product,
        SUM(Value) AS T1Value
    FROM Table1
    GROUP BY Counterparty, Product
),
Table2Totals AS (
    -- 统计Table2分组合计
    SELECT 
        Counterparty,
        Product,
        SUM(Value) AS T2Value
    FROM Table2
    GROUP BY Counterparty, Product
),
CombinedData AS (
    -- 合并两张表数据，确保无遗漏
    SELECT 
        COALESCE(t1.Counterparty, t2.Counterparty) AS Counterparty,
        COALESCE(t1.Product, t2.Product) AS Product,
        ISNULL(t1.T1Value, 0) AS T1Value,
        ISNULL(t2.T2Value, 0) AS T2Value
    FROM Table1Totals t1
    FULL JOIN Table2Totals t2
        ON t1.Counterparty = t2.Counterparty AND t1.Product = t2.Product
),
PivotedData AS (
    -- 透视生成Product列
    SELECT 
        Counterparty,
        ' + @cols + ',
        ' + @colsSum + ' AS Total,
        -- 计算col1：Table2对应Counterparty的合计
        (SELECT SUM(T2Value) FROM CombinedData cd2 WHERE cd2.Counterparty = cd.Counterparty) AS col1
    FROM CombinedData cd
    PIVOT (
        SUM(T1Value)
        FOR Product IN (' + REPLACE(REPLACE(@cols, 'ISNULL(', ''), ' AS [', '], [') + ')
    ) AS pvt
    GROUP BY Counterparty
)
-- 输出结果并生成总计行
SELECT 
    ISNULL(Counterparty, ''Total'') AS Counterparty,
    SUM(Bar) AS Bar,
    SUM(Buzz) AS Buzz,
    SUM(Total) AS Total,
    SUM(col1) AS col1,
    SUM(Total - col1) AS col2,
    0 AS col3,
    SUM(Total) AS col4
FROM PivotedData
GROUP BY ROLLUP(Counterparty)
ORDER BY CASE WHEN Counterparty IS NULL THEN 1 ELSE 0 END, Counterparty'

-- 执行动态SQL
EXEC sp_executesql @query

代码说明 #

1. 先分别统计两张表的分组合计，避免重复计算
2. 使用FULL JOIN确保所有Counterparty和Product都被包含
3. 透视后计算Total和col1，再推导col2、col3、col4
4. 通过ROLLUP自动生成总计行，汇总各列数值
5. 动态生成Product列，适配未来新增的产品类型

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