SQL Server Management Studio concatenate multiple rows using FOR XML PATH

Question

I'm having again a bit of trouble implementing the XML PATH method of concatenating multiple rows.

Given the following SQL Server temptable:

project    |    NAME    |      display_name
-----------+------------+-------------------
  a         Developer         Amelia     
  a         Developer         Ruby
  a         Administrator     Olivia     
  b         User              Isla    
  b         Developer         Emily   
  c         User              Poppy     
  c         Administrator     Ava    
  c         Administrator     James

The result should be:

project    |    NAME        | display_name
-----------+----------------+----------------
  a         Developer         Amelia, Ruby
            Administrator     Olivia

  b         User              Isla    
            Developer         Emily   

  c         User              Poppy     
            Administrator     Ava,James

Is something like this even possible with XML PATH etc. ?

Your query is a simple case of the usual solution. Could you post it to see what's going wrong? — George Menoutis
– George Menoutis, Commented Nov 19, 2018 at 10:18

Yogesh Sharma · Accepted Answer · 2018-11-19 10:22:26Z

1

Yes you can use FOR XML PATH() clause :

SELECT DISTINCT t.project, t.name, STUFF(t1.display_name, 1, 1, '') AS display_name
FROM table t CROSS APPLY
     (SELECT ', '+t1.display_name 
      FROM table t1
      WHERE t1.project = t.project AND t1.name = t.name
      FOR XML PATH('')
     ) t1(display_name);

answered Nov 19, 2018 at 10:22

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

1 Comment

Marc Guillot Over a year ago

I like your sintaxis of calling the subquery on a cross apply and removing the trailing , on the result. Much cleaner, I will use it.

Collectives™ on Stack Overflow

SQL Server Management Studio concatenate multiple rows using FOR XML PATH

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related