FOR XML PATH

Recently I was looking into one of the issues that is bugging for some time ,its implemented in sql query that does a concatenation of the rows that

are identical, initially i thought of implementing so many ways without using a cursor like case when ,joins etc. but when the data is getting,more and more dynamically ...oops... finally got a nice article from stack over flow ....

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

If there is a table called Users

Username Role
---------- -------------
1                Admin
1                Manager
1                Customer
2                Team
2                Operator
Result I expected was:


Username Role
---------- -------------
1               Admin,Manager,Customer
2               Team,Operator
Following is the T-SQL that does this job...

SELECT Temp.username,LEFT(Temp.[Role],Len(Temp.[Role])-1) As 'Role'
FROM( SELECT DISTINCT U2.UserName,(SELECT U1.[Role] + ',' AS [text()]
FROM Users U1
WHERE U1.UserName = U2.UserName
ORDER BY U1.UserName
FOR XML PATH ('')) [Role]
FROM Users U2
) Temp

So what it does is the query uses the For XML for the role column rows and get as a single column separated by commas

I was not aware of this ....seems interesting and learning so put in my memory’s.........