“Stuff and 'For Xml Path'” or UNION to exclude duplicate rows

Question

This is further progress of question here: how to use "Stuff and 'For Xml Path'" to unite rows in table

I have 3 tables - invoices, positions and accounts which build common table by SELECT as specified in result below. I need to exclude duplicate rows which appear due to different accounts for same invoices. I think it can be solved by “Stuff and 'For Xml Path'” and/or by UNION, but I don't really know how to do that.

Please assist with this issue. Thank You in advance.

Here's dbfiddle with DL and sample data population: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6401c2886a24b21239dade27e8c549ec

Using DevExpress dashboard designer based on SQL Server 2016.

Query:

-- DDL and sample data population, start
CREATE TABLE invoices
(
invoice VARCHAR(20) NOT NULL,
id INT NOT NULL,
);
INSERT invoices(invoice, id) 
VALUES ('ty20210110', 1);
INSERT invoices(invoice, id) 
VALUES ('ty20210111', 2);
INSERT invoices(invoice, id) 
VALUES ('ty20210112', 3);

CREATE TABLE positions
(
position VARCHAR(20) NOT NULL,
quantity INT NOT NULL,
price INT NOT NULL,
summ INT NOT NULL, 
invoice INT NOT NULL,
);
INSERT positions(position, quantity, price, summ, invoice) 
VALUES ('p1000001', 2, 100, 200, 1);
INSERT positions(position, quantity, price, summ, invoice) 
VALUES ('p1000002', 3, 100, 300, 2);
INSERT positions(position, quantity, price, summ, invoice) 
VALUES ('p1000003', 1, 250, 250, 2);
INSERT positions(position, quantity, price, summ, invoice) 
VALUES ('p1000004', 2, 120, 240, 1);
INSERT positions(position, quantity, price, summ, invoice) 
VALUES ('p1000005', 4, 100, 400, 1);
INSERT positions(position, quantity, price, summ, invoice) 
VALUES ('p1000006', 3, 100, 300, 1001);
INSERT positions(position, quantity, price, summ, invoice) 
VALUES ('p1000007', 5, 80, 400, 3);
INSERT positions(position, quantity, price, summ, invoice) 
VALUES ('p1000008', 5, 100, 500, 3);

CREATE TABLE accounts
(
account INT NOT NULL,
invoice INT NOT NULL,
);
INSERT accounts(account, invoice) 
VALUES (01, 2);
INSERT accounts(account, invoice) 
VALUES (02, 2);
INSERT accounts(account, invoice) 
VALUES (03, 1);
INSERT accounts(account, invoice) 
VALUES (04, 3);
INSERT accounts(account, invoice) 
VALUES (05, 1);
INSERT accounts(account, invoice) 
VALUES (06, 3);
-- DDL and sample data population, end


SELECT
positions.position, positions.quantity, positions.price, positions.summ,
invoices.invoice,
accounts.account

FROM
positions
INNER JOIN
invoices invoices ON invoices.id = positions.invoice
INNER JOIN
accounts accounts ON invoices.id = accounts.invoice

Result:

position	quantity	price	summ	invoice	account
p1000001	2	100	200	in20210110	3
p1000001	2	100	200	in20210110	5
p1000002	3	100	300	in20210111	1
p1000002	3	100	300	in20210111	2
p1000003	1	250	250	in20210111	1
p1000003	1	250	250	in20210111	2
p1000004	2	120	240	in20210110	3
p1000004	2	120	240	in20210110	5
p1000005	4	100	400	in20210110	3
p1000005	4	100	400	in20210110	5
p1000006	3	100	300	in20210110	3
p1000006	3	100	300	in20210110	5
p1000007	5	80	400	in20210112	4
p1000007	5	80	400	in20210112	6
p1000008	5	100	500	in20210112	4
p1000008	5	100	500	in20210112	6

Required result 1:

position	quantity	price	summ	invoice	account
p1000001	2	100	200	in20210110	3, 5
p1000004	2	120	240	in20210110	3, 5
p1000005	4	100	400	in20210110	3, 5
p1000006	3	100	300	in20210110	3, 5
p1000002	3	100	300	in20210111	1, 2
p1000003	1	250	250	in20210111	1, 2
p1000007	5	80	400	in20210112	4, 6
p1000008	5	100	500	in20210112	4, 6

Required result 2:

position	quantity	price	summ	invoice	account 1	account 2
p1000001	2	100	200	in20210110	3	5
p1000004	2	120	240	in20210110	3	5
p1000005	4	100	400	in20210110	3	5
p1000006	3	100	300	in20210110	3	5
p1000002	3	100	300	in20210111	1	2
p1000003	1	250	250	in20210111	1	2
p1000007	5	80	400	in20210112	4	6
p1000008	5	100	500	in20210112	4	6

Which version of SQL Server are you using? In Result2, how many columns do you need? 2? 100? 1000? @Larnu Result1 does have — Charlieface, Feb 17 '21 at 20:38

critical_error · Accepted Answer · 2021-02-17T21:56:30.640

Your first resultset can be handled in two ways, depending on your version of SQL Server. Try the following examples in SSMS:

Create Data

DECLARE @invoices table (
    invoice VARCHAR(20) NOT NULL, id INT NOT NULL 
);

INSERT INTO @invoices (invoice, id) VALUES 
    ( 'ty20210110', 1 ), ( 'ty20210111', 2 ), ( 'ty20210112', 3 );

DECLARE @positions table (
    position VARCHAR(20) NOT NULL, quantity INT NOT NULL, price INT NOT NULL, summ INT NOT NULL, invoice INT NOT NULL
);

INSERT INTO @positions ( position, quantity, price, summ, invoice ) VALUES
    ( 'p1000001', 2, 100, 200, 1 ),
    ( 'p1000002', 3, 100, 300, 2 ),
    ( 'p1000003', 1, 250, 250, 2 ),
    ( 'p1000004', 2, 120, 240, 1 ),
    ( 'p1000005', 4, 100, 400, 1 ),
    ( 'p1000006', 3, 100, 300, 1 ),
    ( 'p1000007', 5, 80, 400, 3 ),
    ( 'p1000008', 5, 100, 500, 3 );

DECLARE @accounts table (
    account INT NOT NULL, invoice INT NOT NULL
);

INSERT INTO @accounts ( account, invoice ) VALUES 
    ( 1, 2 ), ( 2, 2 ), ( 3, 1 ), ( 4, 3 ), ( 5, 1 ), ( 6, 3 );

If you're using SQL Server 2017+

SELECT
    positions.position,
    positions.quantity,
    positions.price,
    positions.summ,
    invoices.invoice,
    STRING_AGG ( accounts.account, ',' ) AS account
FROM @positions positions
INNER JOIN @invoices invoices 
    ON invoices.id = positions.invoice
INNER JOIN @accounts accounts 
    ON invoices.id = accounts.invoice
GROUP BY
    position, quantity, price, summ, invoices.invoice
ORDER BY
    invoice;

For Pre-SQL Server 2017

SELECT
    positions.position,
    positions.quantity,
    positions.price,
    positions.summ,
    invoices.invoice,
    acct.account
FROM @positions positions
INNER JOIN @invoices invoices 
    ON invoices.id = positions.invoice
INNER JOIN @accounts accounts 
    ON invoices.id = accounts.invoice
OUTER APPLY (
    SELECT STUFF ( (
        SELECT ',' + CAST ( a.account AS varchar(50) ) AS "text()"
        FROM @accounts a
        WHERE
            a.invoice = invoices.id
        FOR XML PATH ( '' )
    ), 1, 1, '' ) AS account
) AS acct
GROUP BY
    position, quantity, price, summ, invoices.invoice, acct.account
ORDER BY
    invoice;

Both queries return

+----------+----------+-------+------+------------+---------+
| position | quantity | price | summ |  invoice   | account |
+----------+----------+-------+------+------------+---------+
| p1000001 |        2 |   100 |  200 | ty20210110 | 3,5     |
| p1000004 |        2 |   120 |  240 | ty20210110 | 3,5     |
| p1000005 |        4 |   100 |  400 | ty20210110 | 3,5     |
| p1000006 |        3 |   100 |  300 | ty20210110 | 3,5     |
| p1000002 |        3 |   100 |  300 | ty20210111 | 1,2     |
| p1000003 |        1 |   250 |  250 | ty20210111 | 1,2     |
| p1000007 |        5 |    80 |  400 | ty20210112 | 4,6     |
| p1000008 |        5 |   100 |  500 | ty20210112 | 4,6     |
+----------+----------+-------+------+------------+---------+

The second resultset you requested gets considerably more complicated and requires the use of the XML datatype. The following example makes liberal assumptions of your data, most notably that only two accounts are expected. Having said that, you can expand the [account n] columns in the PIVOT as necessary without having to resort to dynamic SQL.

SELECT DISTINCT
    positions.position,
    positions.quantity,
    positions.price,
    positions.summ,
    invoices.invoice
    , x.*
FROM @positions positions
INNER JOIN @invoices invoices 
    ON invoices.id = positions.invoice
INNER JOIN @accounts accounts 
    ON invoices.id = accounts.invoice
OUTER APPLY (

    -- Create an XML account list --
    SELECT CAST ( (
        SELECT 
            'account ' + CAST ( ROW_NUMBER() OVER ( ORDER BY a.invoice ) AS varchar(50) ) AS id,
            a.account
        FROM @accounts a
        WHERE
            a.invoice = invoices.id
        FOR XML PATH ( 'data' ), ROOT ( 'accounts' )
    ) AS xml ) AS account_xml

) AS acct
OUTER APPLY (

    -- PIVOT the account details --
    SELECT
        *
    FROM (
        SELECT
            x.f.value( 'id[1]', 'varchar(50)' ) AS id,
            x.f.value( 'account[1]', 'varchar(50)' ) AS act
        FROM acct.account_xml.nodes( '//accounts/data' ) x(f)
    ) AS d
    PIVOT (
        MAX ( act ) FOR id IN ( [account 1], [account 2] )
    ) AS p

) AS x
ORDER BY
    invoice;

Returns

+----------+----------+-------+------+------------+-----------+-----------+
| position | quantity | price | summ |  invoice   | account 1 | account 2 |
+----------+----------+-------+------+------------+-----------+-----------+
| p1000001 |        2 |   100 |  200 | ty20210110 |         3 |         5 |
| p1000004 |        2 |   120 |  240 | ty20210110 |         3 |         5 |
| p1000005 |        4 |   100 |  400 | ty20210110 |         3 |         5 |
| p1000006 |        3 |   100 |  300 | ty20210110 |         3 |         5 |
| p1000002 |        3 |   100 |  300 | ty20210111 |         1 |         2 |
| p1000003 |        1 |   250 |  250 | ty20210111 |         1 |         2 |
| p1000007 |        5 |    80 |  400 | ty20210112 |         4 |         6 |
| p1000008 |        5 |   100 |  500 | ty20210112 |         4 |         6 |
+----------+----------+-------+------+------------+-----------+-----------+

I don't know why but 2nd solution shows empty cells in my work query (http://prntscr.com/zytby7) but in dbfiddle works properly. You can check my full work query in dbfiddle here (just to see script, it definitely doesn't work there): https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=0eb928dcc1ba5b98300288abe84d5379 — user421875, Feb 18 '21 at 10:39
@user421875 is it possible the data used here doesn't match your data at work? Without having access to your actual data (that's not a request) it's nearly impossible to tell from a screenshot. — critical_error, Feb 18 '21 at 17:21
Hi! Maybe because we don't need to convert soome to varchars? http://prntscr.com/100nlcn — user421875, Feb 19 '21 at 09:28
by the way, in my work table accounts may have more than 2 instances per same invoice, maybe this caused my problem with 2nd solution - we add only 2 account columns. Anyways, 1st solution is totally fit to my need, so thanx a lot again for it. — user421875, Feb 20 '21 at 14:43

“Stuff and 'For Xml Path'” or UNION to exclude duplicate rows

1 Answers1