Tuesday, October 16, 2012


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 ....


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.........