Add custom columns to pivot table

Given two tables like this:

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

I need to produce an output like this:

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

where

• col1 is the same as Total but from table2
• col2 is the difference betweeen Total and col1
• col3 should be filled with 0```
• col4 is the difference between Total and col3

So far I can create a dynamic SQL query, but I can't figure out how to add the extra columns and perform the necessary operations:

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);

OUTPUT:

Counterparty   Bar      Buzz
Fizz           20.00    20.00
Foo            40.00    0.00

I assume I need to JOIN the extra column values in my CTE expression with a table that matches the extra columns to each counterparty but I am having a hard time visualizing it and creating a query.

I would take a different approach. First UNION ALL the two sources after adding a source = 1 or 2 column. Next, group the combined data on Counterparty and use conditional aggregation to selectively sum the values for each of the remaining result columns.

Conditional aggregation is an aggregation function (like MAX(), SUM() or COUNT()) that includes a conditional CASE WHEN condition THEN value END. When the condition is true, the THEN value is included in the result. When false, the implied ELSE NULL is ignored. In some cases it makes sense to add an ELSE 0 to avoid null results.

To add the Totals row, you can add the WITH ROLLUP option to the GROUP BY and add some logic to conditionally set the Counterparty column to 'Total'.

SELECT
    CASE WHEN GROUPING(Counterparty) = 0 THEN Counterparty
        ELSE 'Total' END AS Counterparty,
    SUM(CASE WHEN Src = 1 AND Product = 'bar' THEN Value ELSE 0 END) AS Bar,
    SUM(CASE WHEN Src = 1 AND Product = 'buzz' THEN Value ELSE 0 END) AS Buzz,
    SUM(CASE WHEN Src = 1 THEN Value ELSE 0 END) AS Total,
    SUM(CASE WHEN Src = 2 THEN Value ELSE 0 END) AS Col1,
    SUM(CASE WHEN Src = 1 THEN Value ELSE 0 END)
        - SUM(CASE WHEN Src = 2 THEN Value ELSE 0 END) AS Col2,
    0 AS Col3,
    SUM(CASE WHEN Src = 1 THEN Value ELSE 0 END) - 0 AS Col4
FROM (
    SELECT *, Src = 1 FROM Table1
    UNION ALL
    SELECT *, Src = 2 FROM Table2
) U
GROUP BY Counterparty WITH ROLLUP
ORDER BY GROUPING(Counterparty), Counterparty;

Here is a dynamic SQL version:

DECLARE @cols AS NVARCHAR(MAX) = (
    SELECT STRING_AGG(
        'SUM(CASE WHEN Src = 1 AND Product = '
            + QUOTENAME(Product, '''')  -- limit 128 characters
            + ' THEN Value ELSE 0 END) AS '
            + QUOTENAME(Product)
            + ',',
        CHAR(13) + CHAR(10) + SPACE(8)
        ) WITHIN GROUP(ORDER BY Product)
    FROM (
        SELECT Product FROM Table1
        UNION -- Implicit distinct
        SELECT Product FROM Table2
    ) U
);

DECLARE @query AS NVARCHAR(MAX) = '
    SELECT
        CASE WHEN GROUPING(Counterparty) = 0 THEN Counterparty ELSE ''Total'' END AS Counterparty,
        ' + @Cols + '
        SUM(CASE WHEN Src = 1 THEN Value ELSE 0 END) AS Total,
        SUM(CASE WHEN Src = 2 THEN Value ELSE 0 END) AS Col1,
        SUM(CASE WHEN Src = 1 THEN Value ELSE 0 END) - SUM(CASE WHEN Src = 2 THEN Value ELSE 0 END) AS Col2,
        0 AS Col3,
        SUM(CASE WHEN Src = 1 THEN Value ELSE 0 END) - 0 AS Col4
    FROM (
        SELECT *, Src = 1 FROM Table1
        UNION ALL
        SELECT *, Src = 2 FROM Table2
    ) U
    GROUP BY Counterparty WITH ROLLUP
    ORDER BY GROUPING(Counterparty), Counterparty;
';

EXEC (@query);

The above builds the dynamic column list uses the STRING_AGG(), which is available in SQL Server 2017 and later. For earlier versions, the following FOR XML technique can be used:

DECLARE @cols AS NVARCHAR(MAX) = STUFF((
    SELECT
        CHAR(13) + CHAR(10) + SPACE(8) -- 10 characters
            + 'SUM(CASE WHEN Src = 1 AND Product = '
            + QUOTENAME(Product, '''')  -- limit 128 characters
            + ' THEN Value ELSE 0 END) AS '
            + QUOTENAME(Product)
            + ','
    FROM (
        SELECT Product FROM Table1
        UNION -- Implicit distinct
        SELECT Product FROM Table2
    ) U
    ORDER BY Product
    FOR XML PATH(''), TYPE
    ).value('text()[1]','nvarchar(max)'), 1, 10, '');

Note that the @cols = COALESCE(@cols + ', ', '') + ... FROM ... technique from the original post depends on undocumented behavior that is not guaranteed to work, so is best to avoid.

Results:

See this SQL Fiddle for a demo of both the fixed and dynamic SQL versions.

An even better solution may be to feed the query the following combined data source:

SELECT *, Value AS Value1, 0 * Value AS Value2  FROM Table1
UNION ALL
SELECT *, 0 * Value AS Value1, Value AS Value2  FROM Table2

Here either Value1 or Value2 is set, depending on the source. (The 0 * Value calculation is used instead of just 0 to preserve the data type.)

The aggregated value calculations are now simplified to:

    SUM(CASE WHEN Product = 'bar' THEN Value1 ELSE 0 END) AS Bar,
    SUM(CASE WHEN Product = 'buzz' THEN Value1 ELSE 0 END) AS Buzz,
    SUM(Value1) AS Total,
    SUM(Value2) AS Col1,
    SUM(Value1 - Value2) AS Col2,
    0 AS Col3,
    SUM(Value1) - 0 AS Col4

See this SQL Fiddle for a demo of this approach, including dynamic SQL.