CODEWITHSUNDEEP
sql-serverconcatenationfor-xml-pathstuff
user421875
user421875

Reputation: 15

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 TABLE invoices
(
invoice VARCHAR(20) NOT NULL,
quantity INT NOT NULL,
price INT NOT NULL,
summ INT NOT NULL, 
account INT NOT NULL,
);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 2, 100, 200, 1001);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 3, 100, 300, 1002);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 1, 250, 250, 1001);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 2, 120, 240, 1002);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210110', 4, 100, 400, 1002);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210114', 3, 100, 300, 1001);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210114', 5, 80, 400, 1003);
INSERT invoices(invoice, quantity, price, summ, account) 
VALUES ('ty20210114', 5, 100, 500, 1004);


SELECT invoices.invoice, invoices.summ,  accounts = STUFF(
             (SELECT DISTINCT ',' + Convert(varchar, invoices.account, 60) 
              FROM invoices
              FOR XML PATH (''))
             , 1, 1, '')
FROM invoices
GROUP BY invoices.invoice, invoices.summ

This is what I get in result:

invoice summ accounts
ty20210110 200 1001,1002,1003,1004
ty20210110 240 1001,1002,1003,1004
ty20210110 250 1001,1002,1003,1004
ty20210110 300 1001,1002,1003,1004
ty20210110 400 1001,1002,1003,1004
ty20210114 300 1001,1002,1003,1004
ty20210114 400 1001,1002,1003,1004
ty20210114 500 1001,1002,1003,1004

This is what I need to get in result:

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

So actually I need to get sums for 2 different invoices and to specify accounts by commas which involved to those invoices.

Also have this stuff at dbfiddle here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7a5de9e680693b5e70ea68cecebef6cc

Thank You in advance guys.

Upvotes: 1

Views: 6537

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22311

For SQL Server 2017 onwards.

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

SQL

-- DDL and sample data population, start
DECLARE @invoices TABLE 
(
    invoice VARCHAR(20) NOT NULL,
    quantity INT NOT NULL,
    price INT NOT NULL,
    summ INT NOT NULL, 
    account INT NOT NULL
);
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, end

SELECT i1.invoice
    , SUM(i1.summ) AS summ
    , (
       SELECT STRING_AGG(account,',') FROM 
       (
        (SELECT DISTINCT account FROM @invoices AS i2 WHERE i2.invoice = i1.invoice)
       ) AS x) AS accounts
FROM @invoices AS i1
GROUP BY i1.invoice;

Output

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

Upvotes: 0

sticky bit
sticky bit

Reputation: 37487

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((SELECT DISTINCT
                     concat(',', i2.account)
                     FROM invoices i2
                     WHERE i2.invoice = i1.invoice
                     FOR XML PATH ('')),
             1,
             1,
             '') accounts
       FROM invoices i1
       GROUP BY i1.invoice;

Upvotes: 1

Related Questions