FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE Table1 (
    Counterparty VARCHAR(10),
    Product VARCHAR(10),
    Deal VARCHAR(10),
    Date DATE,
    Value NUMERIC(10,2)
);
CREATE TABLE Table2 (
    Counterparty VARCHAR(10),
    Product VARCHAR(10),
    Deal VARCHAR(10),
    Date DATE,
    Value NUMERIC(10,2)
);

INSERT INTO Table1
VALUES
    ('foo', 'bar', 'Buy', '2024-01-01', 10.00),
    ('foo', 'bar', 'Buy', '2024-01-01', 10.00),
    ('foo', 'bar', 'Sell', '2024-01-01', 10.00),
    ('foo', 'bar', 'Sell', '2024-01-01', 10.00),
    ('fizz', 'bar', 'Buy', '2024-01-01', 10.00),
    ('fizz', 'bar', 'Buy', '2024-01-01', 10.00),
    ('fizz', 'buzz', 'Sell', '2024-01-01', 10.00),
    ('fizz', 'buzz', 'Sell', '2024-01-01', 10.00);
INSERT INTO Table2
VALUES
    ('foo', 'bar', 'Buy', '2024-01-01', 11.00),
    ('foo', 'bar', 'Buy', '2024-01-01', 09.00),
    ('foo', 'bar', 'Sell', '2024-01-01', 09.00),
    ('foo', 'bar', 'Sell', '2024-01-01', 10.00),
    ('fizz', 'bar', 'Buy', '2024-01-01', 12.00),
    ('fizz', 'bar', 'Buy', '2024-01-01', 08.00),
    ('fizz', 'buzz', 'Sell', '2024-01-01', 09.00),
    ('fizz', 'buzz', 'Sell', '2024-01-01', 10.00);

--------------------------------------------------
--  Static SQL solution
--------------------------------------------------

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;

--------------------------------------------------
--  Static SQL solution
--------------------------------------------------

SET QUOTED_IDENTIFIER ON

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

-- For earlier SQL Server versions
DECLARE @cols2 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, '');

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

PRINT ''
PRINT @Cols
PRINT ''
PRINT @Cols2
PRINT ''

PRINT @query;
EXEC (@query);

Counterparty	Bar	Buzz	Total	Col1	Col2	Col4
fizz	20.00	20.00	40.00	39.00	1.00	40.00
foo	40.00	.00	40.00	39.00	1.00	40.00
Total	60.00	20.00	80.00	78.00	2.00	80.00

Counterparty	bar	buzz	Total	Col1	Col2	Col4
fizz	20.00	20.00	40.00	39.00	1.00	40.00
foo	40.00	.00	40.00	39.00	1.00	40.00
Total	60.00	20.00	80.00	78.00	2.00	80.00

(8 rows affected)

(8 rows affected)

(3 rows affected)

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 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],

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;

(3 rows affected)