Concatenate data in a single row [duplicate]

Question

I have the following table:

    TicketID                                Type       Code
    44899B82-41C9-4020-9C2B-007C6000D283    ARTI     22150012
    44899B82-41C9-4020-9C2B-007C6000D283    ARTI     22150017
    44899B82-41C9-4020-9C2B-007C6000D283    CASH     NULL
    76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    ARTI     22150018
    76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    ARTI     22150012
    76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    ARTI     22150011
    76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    CASH     NULL

And I want the following result:

    TicketID                                Type       Code
    44899B82-41C9-4020-9C2B-007C6000D283    ARTI     22150012, 22150017
    44899B82-41C9-4020-9C2B-007C6000D283    CASH     NULL
    76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    ARTI     22150018, 22150012, 22150011 
    76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    CASH     NULL

How can I achieve that?

Answer 1

You must concatenate those values. The best way is to use FOR XML PATH.

Query (SQL Fiddle):

SELECT [TicketID], [Type]
    , [Code] = stuff(
        (
            SELECT ', '+ [Code] 
            FROM data d2
            WHERE [TicketID] = d1.[TicketID] AND [Type] = d1.[Type]
            FOR XML PATH('')
        ), 1, 1, ''
    ) 
FROM (SELECT DISTINCT [TicketID], [Type] FROM data) d1

Output:

TicketID                                Type    Code
44899B82-41C9-4020-9C2B-007C6000D283    ARTI    22150012, 22150017
44899B82-41C9-4020-9C2B-007C6000D283    CASH    NULL
76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    ARTI    22150018, 22150012, 22150011
76C6D696-55E7-4CA5-A1AA-0F0DB446D9A4    CASH    NULL

If [TicketID] and [Type] are in table Line and [Code] in table Article, LEFT JOIN must be used (SQL Fiddle):

SELECT [TicketID], [Type]
    , [Code] = stuff(
        (
            SELECT ', '+ [Code] 
            FROM Line l2
            LEFT JOIN Article a ON a.ID = l2.ArticleId
            WHERE [TicketID] = l1.[TicketID] AND [Type] = l1.[Type]
            FOR XML PATH('')
        ), 1, 1, ''
    ) 
FROM (SELECT [TicketID], [Type] FROM Line) l1