how to use "Stuff and 'For Xml Path'" to unite rows in table

Question

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.

What is your SQL Server version? – Yitzhak Khabinsky Feb 16 '21 at 20:49 — Yitzhak Khabinsky, Feb 16 '21 at 20:49

score 1 · Accepted Answer · answered Feb 16 '21 at 20:46

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;

`FOR XML PATH(''), TYPE)).value('text()[1]','nvarchar(max)'), 1...` would be best — Charlieface, Feb 16 '21 at 20:51

Yitzhak Khabinsky · Answer 2 · 2021-02-16T21:16:42.083

0

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 |
+------------+------+----------------+

edited Feb 16 '21 at 21:16

answered Feb 16 '21 at 21:09

Yitzhak Khabinsky

18,471
2
15
21

Hello, it looks okay, but what if I have account queryfrom another table? – user421875 Feb 17 '21 at 06:58
@user421875, glad to hear that the proposed solution is working for you. Please don't forget to mark it as Answer. – Yitzhak Khabinsky Feb 17 '21 at 13:30
@user421875, while asking a question you need to provide a minimal reproducible example. You did that via dbfiddle, and I followed it. Please ask another question to cover **"...but what if I have account queryfrom another table?..."** – Yitzhak Khabinsky Feb 17 '21 at 13:33
sorry, going to do that a bit later - to much work todays. Thanx for response – user421875 Feb 17 '21 at 14:25
I created query maximally close to original one from my work dashboard. It's here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d4868c6871b04ed87836bc8620610ad3 If You look at screenshot here http://prntscr.com/zvpqle You'll find duplicate rows because of different accounts. So I wanted to concat them and to put accounts for same invoices to same row, separated by commas. I thought that “Stuff and 'For Xml Path'” will help me because I've seen it in some another dashboard query, but I don't understand how to properly set it up. Could You please assist with this issue? – user421875 Feb 17 '21 at 17:54
@user421875, please mark this question as answered, and create a brand new question for your new question. And learn from my answer how to ask a question: -- by providing `DDL and sample data population` – Yitzhak Khabinsky Feb 17 '21 at 18:10

how to use "Stuff and 'For Xml Path'" to unite rows in table

2 Answers2

Linked