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
Result I expected was:
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
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.........