How To Use "stuff And 'for Xml Path'" To Unite Rows In Table

Please help me to get united rows and list of accounts separated by commas in table. I don't quite understand how to use 'Stuff and 'For Xml Path'' for it. This is my query: CREATE

Solution 1:

Don't group by summ if you want to sum it. Use sum() on it. And correlate the subquery. Otherwise you'll just get all accounts.

SELECT i1.invoice,
       sum(i1.summ) summ,
       stuff((SELECTDISTINCT
                     concat(',', i2.account)
                     FROM invoices i2
                     WHERE i2.invoice = i1.invoice
                     FOR XML PATH ('')),
             1,
             1,
             '') accounts
       FROM invoices i1
       GROUPBY i1.invoice;

Solution 2:

For SQL Server 2017 onwards.

Leveraging the STRING_AGG() function. The only nuance is that we need to select DISTINCTaccount value in the sub-query.

SQL

-- DDL and sample data population, startDECLARE@invoicesTABLE 
(
    invoice VARCHAR(20) NOTNULL,
    quantity INTNOTNULL,
    price INTNOTNULL,
    summ INTNOTNULL, 
    account INTNOTNULL
);
INSERT@invoices(invoice, quantity, price, summ, account) VALUES 
('ty20210110', 2, 100, 200, 1001),
('ty20210110', 3, 100, 300, 1002),
('ty20210110', 1, 250, 250, 1001),
('ty20210110', 2, 120, 240, 1002),
('ty20210110', 4, 100, 400, 1002),
('ty20210114', 3, 100, 300, 1001),
('ty20210114', 5, 80, 400, 1003),
('ty20210114', 5, 100, 500, 1004);
-- DDL and sample data population, endSELECT i1.invoice
    , SUM(i1.summ) AS summ
    , (
       SELECT STRING_AGG(account,',') FROM 
       (
        (SELECTDISTINCT account FROM@invoicesAS i2 WHERE i2.invoice = i1.invoice)
       ) AS x) AS accounts
FROM@invoicesAS i1
GROUPBY i1.invoice;

Output

+------------+------+----------------+|invoice|summ|accounts|+------------+------+----------------+|ty20210110|1390|1001,1002||ty20210114|1200|1001,1003,1004|+------------+------+----------------+