Using XML PATH to concatenate multiple fields

Question

I'm having a bit of trouble implementing the XML PATH method of concatenating multiple rows. So, given the following table, Test.

ID  Type  
 1  boy
 2  boy
 3  boy
 1  girl
 2  girl
 3  man
 3  woman

The query is:

SELECT DISTINCT a.ID,
    (
        SELECT  b.Type + ','
        FROM Test as b
        WHERE a.Type = b.Type
        for XML PATH ('')
    )
FROM Test as a

but instead of returning:

ID  Type  
 1  boy,girl,man,
 2  boy,girl,
 3  boy,girl,woman

it instead returns this:

ID  Type
 1  boy,boy,boy,
 1  girl,girl,
 2  boy,boy,boy,
 2  girl,girl,
 3  boy,boy,boy,
 3  man,
 3  woman,

What's going on?

podiluska · Accepted Answer · 2014-03-27 14:39:36Z

1

You're joining on the wrong field.

Try

SELECT DISTINCT a.ID,
    (
        SELECT  b.Type + ','
        FROM Test as b
        WHERE a.ID = b.ID
        for XML PATH ('')
    )
FROM Test as a

answered Mar 27, 2014 at 14:39

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Go-Soco Over a year ago

Indeed I am! Thank you.

M.Ali · Accepted Answer · 2014-03-27 16:44:53Z

1

Instead of using DISTINCT prefer using GROUP BY something like this...

SELECT  a.ID,

   STUFF((SELECT  ', ' +  [Type] [text()]
          FROM Test 
          WHERE ID = a.[ID]
          for XML PATH (''),TYPE).
          value('.','NVARCHAR(MAX)'),1,2,'') AS [Type]

FROM Test as a
GROUP BY a.ID

answered Mar 27, 2014 at 16:44

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

1 Comment

M.Ali Over a year ago

@lunarplasma Yes certainly using group by instead of DISTINCT can give you much better performance.

Collectives™ on Stack Overflow

Using XML PATH to concatenate multiple fields

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related