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 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
FROM (
    SELECT *, Value AS Value1, 0 * Value AS Value2  FROM Table1
    UNION ALL
    SELECT *, 0 * Value AS Value1, Value AS Value2  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 Product = '
            + QUOTENAME(Product, '''')  -- limit 128 characters
            + ' THEN Value1 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 Product = '
			+ QUOTENAME(Product, '''')  -- limit 128 characters
            + ' THEN Value1 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(Value1) AS Total,
        SUM(Value2) AS Col1,
        SUM(Value1 - Value2) AS Col2,
        0 AS Col3,
        SUM(Value1) - 0 AS Col4
    FROM (
    SELECT *, Value AS Value1, 0 * Value AS Value2  FROM Table1
    UNION ALL
    SELECT *, 0 * Value AS Value1, Value AS Value2  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 Product = 'bar' THEN Value1 ELSE 0 END) AS [bar],
SUM(CASE WHEN Product = 'buzz' THEN Value1 ELSE 0 END) AS [buzz],

SUM(CASE WHEN Product = 'bar' THEN Value1 ELSE 0 END) AS [bar],
SUM(CASE WHEN Product = 'buzz' THEN Value1 ELSE 0 END) AS [buzz],

SELECT
CASE WHEN GROUPING(Counterparty) = 0 THEN Counterparty ELSE 'Total' END AS Counterparty,
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
FROM (
SELECT *, Value AS Value1, 0 * Value AS Value2 FROM Table1
UNION ALL
SELECT *, 0 * Value AS Value1, Value AS Value2 FROM Table2
) U
GROUP BY Counterparty WITH ROLLUP
ORDER BY GROUPING(Counterparty), Counterparty;

(3 rows affected)