create a semicolon delimeted list

I am trying to write a select statement  which will return a semicolon delimeted list. Here is what I want.

Currently my select statement is returning me the following resultset

userroleid          childuserroleid
   4                               4
   4                             10
   4                               7
   8                             12
   8                              13
   8                              15
   11                             44
    12                            89

I want the select statement to return following

userroleid          childuserroleid
   4                               4;10;7
   8                             12;13;15
   11                             44
    12                            89


Thanks
PratikShah111Asked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Use STUFF function together with FOR XML PATH output:
SELECT  userroleid,
          STUFF((SELECT ',' + childuserroleid
                    FROM TableName T2
                    WHERE T2.userroleid = T1.userroleid
                    FOR XML PATH('') ), 1, 1, '' )
            AS ConcatenatedText
FROM  TableName T1

Open in new window

0
All Courses

From novice to tech pro — start learning today.