Full invoice number + comma separated SQL list (TSQL)

Question

Does someone perhaps know how to get a comma separated list from SQL that doesn't duplicate - it's a little hard to explain. Let me give an example.

I have a list of invoices + the shipment it belongs to in a table like below:

InvoiceNumber ShipmentNumber
0180376000    1stShipment
0180376005    1stShipment
0180376003    1stShipment
0180375997    1stShipment
0180375993    1stShipment

This list needs to be divided up into main InvoiceNumbers followed by the right 2 digits of the remaining invoice numbers. Result should look similar to the below.

01803760, 00, 05, 03, 01803759, 97, 93

At this point I can get the comma separated list fairly easily but cannot figure out how to position the 2 digit after each respective invoice that it belongs to.

Any suggestion of how to do this would be great!!

Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — , Jul 12 '16 at 12:21

Shnugo · Answer 1 · 2016-07-12T12:17:20.577

2

Try it like this

DECLARE @tbl TABLE(InvoiceNumber VARCHAR(100));
INSERT INTO @tbl VALUES
 ('0180376000')
,('0180376005')
,('0180376003')
,('0180375997')
,('0180375993');

WITH CutInTwo AS
(
    SELECT LEFT(InvoiceNumber,8) AS Number
          ,RIGHT(InvoiceNumber,2) AS SubNumber
    FROM @tbl
)
,OnlyMainNumbers AS
(
    SELECT DISTINCT Number
    FROM CutInTwo
)
SELECT y.Number + ' ' + (STUFF((SELECT ', ' + x.SubNumber FROM CutInTwo AS x WHERE x.Number=y.Number FOR XML PATH('')),1,2,''))
FROM OnlyMainNumbers AS y

If you really need this in one single string, you might wrap the final select like this:

SELECT STUFF(
(
    SELECT ', ' + y.Number + ', ' + (STUFF((SELECT ', ' + x.SubNumber FROM CutInTwo AS x WHERE x.Number=y.Number FOR XML PATH('')),1,2,''))
    FROM OnlyMainNumbers AS y
    FOR XML PATH('')
),1,2,'')

edited Jul 12 '16 at 12:17

answered Jul 12 '16 at 12:11

Shnugo

66,100
9
53
114

Thanks Shnugo - Exactly what I need! – escGoat007 Jul 12 '16 at 12:33
@schnugo - so I've been incorporating the logic you suggested into my application and the only problem I have is getting it to work for more than one Shipment. If I try and use two shipment it contaminates all the invoices to each shipment. `01803759, 93, 97, 01803760, 00, 03, 05, 00900204, 92, 93` instead of making a new row if I have more than one shipment. I added a group by ShipmentNumber when I added it to the select and made the relevant change to the STUFF query. – escGoat007 Jul 12 '16 at 13:46
'SELECT DISTINCT STUFF( ( SELECT ', ' + y.Number + ', ' + (STUFF((SELECT ', ' + x.SubNumber FROM CutInTwo AS x WHERE x.Number=y.Number and x.ShipmentNumber = y.ShipmentNumber FOR XML PATH('')),1,2,'')) FROM OnlyMainNumbers AS y FOR XML PATH('') ),1,2,'') as InvoiceNumber' How would you make it that the invoices would be associated to the correct ShipmentNumber? Thanks again! – escGoat007 Jul 12 '16 at 13:50
@escGoat007 Without knowing your actual problem, I think it should suffice to add a `WHERE ShipmentNumber='SomeNumber'` to the first CTE called `CutInTwo`... – Shnugo Jul 12 '16 at 13:51
@escGoat007 ... Additionally you should add the ShipmentNumber to the second CTE too. With DISTINCT it should return several rows, if the ShipmentNumbers are not the same... – Shnugo Jul 12 '16 at 13:53
@escGoat007 Hi, I'm curious: How did you solve it finally? – Shnugo Jul 17 '16 at 21:00

neer · Accepted Answer · 2016-07-12T12:37:12.833

Try this

DECLARE @tbl TABLE (InvoceNumber NVARCHAR(50))

INSERT INTO @tbl VALUES  ('0180376000')
INSERT INTO @tbl VALUES  ('0180376005')
INSERT INTO @tbl VALUES  ('0180376003')
INSERT INTO @tbl VALUES  ('0180375997')
INSERT INTO @tbl VALUES  ('0180375993')

SELECT  
    (
        SELECT
             A.InvoceNumber + ', ' 
        FROM
        (
            SELECT DISTINCT LEFT(InvoceNumber, LEN(InvoceNumber) - 2) InvoceNumber  FROM @tbl
            UNION ALL
            SELECT RIGHT(InvoceNumber, 2) InvoceNumber FROM @tbl
        ) A
        FOR XML PATH ('')
    ) Invoce

Output:

01803759, 01803760, 00, 05, 03, 97, 93,

If Order is important.

SELECT
(
    SELECT
        Result.InvoceNumber + ', ' + Result.Invo
    FROM
    (
        SELECT
            A.InvoceNumber,
            (
                SELECT
                     IA.Invo   + ', ' AS [text()]
                FROM
                (
                    SELECT DISTINCT LEFT(InvoceNumber, LEN(InvoceNumber) - 2) InvoceNumber, RIGHT(InvoceNumber, 2) AS Invo  FROM @tbl
                ) IA
                WHERE
                    IA.InvoceNumber = A.InvoceNumber
                FOR XML PATH ('')
            ) Invo
        FROM
        (
            SELECT DISTINCT LEFT(InvoceNumber, LEN(InvoceNumber) - 2) AS InvoceNumber FROM @tbl
        ) A
        GROUP BY 
            A.InvoceNumber
    ) Result
    FOR XML PATH ('')
) S

Output:

01803759, 93, 97, 01803760, 00, 03, 05,

This is depending on an implicit sort order, which does not exist... 97 and 93 are bound to another number... — Shnugo, Jul 12 '16 at 12:13
I am not sure If order is important Yes this answer is wrong. — neer, Jul 12 '16 at 12:24
Your approach with `UNION ALL` will put both pieces of the invoice number as a simple running list and you will have no chance to know, which sub-number belongs to which main-number. Even if they were sorted correctly, you could not rely on this order to be the one you see in the final output... — Shnugo, Jul 12 '16 at 12:26
OK, now your solution is very close to mine (I'm using CTEs and you are using sub-selects, which should not make any difference here) but you are missing the final comma... For my personal opinion mine is easier to read, but you've got the acceptance, that's live... Happy coding! — Shnugo, Jul 12 '16 at 13:22

Full invoice number + comma separated SQL list (TSQL)

2 Answers2