CODEWITHSUNDEEP
sql-servert-sql
escGoat007
escGoat007

Reputation: 43

Full invoice number + comma separated SQL list (TSQL)

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?

Upvotes: 2

Views: 288

Answers (2)

neer
neer

Reputation: 4082

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,

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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,'')

Upvotes: 2

Related Questions