Full invoice number + comma separated SQL list (TSQL)

Question

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.

How to do this?

Answer 1

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,