1

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?

1
  • 2
    Been asked SO many times Feb 12, 2016 at 14:32

1 Answer 1

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
8
  • Thanks, but I forgot to mention that TicketID and Type come from a table and Code comes from a different table :)
    – MrSilent
    Feb 12, 2016 at 14:33
  • Just join the 2 table: FROM table1 d2 INNER JOIN table2 ON... Feb 12, 2016 at 14:35
  • Could you please edit your example so I can clearly see this? I am not sure where to put the INNER JOIN
    – MrSilent
    Feb 12, 2016 at 14:38
  • You should know how to join t2 table. This is basic SQL. What are your table names and the column name? Feb 12, 2016 at 14:40
  • Line: TicketID and Type Article: Code
    – MrSilent
    Feb 12, 2016 at 14:41

Not the answer you're looking for? Browse other questions tagged or ask your own question.