serialize the result - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

serialize the result

Author

Topic

smithersgs
Starting Member

17 Posts

Posted - 2008-07-29 : 11:41:12

A question on a query.
I have three roles and a user can be assigned to multiple roles. If I query to get a list of users and their roles I've got the multiple rows for the same user. I want a single record of a user with a field that shows a lits of multiple roles. How can I do that?
Thanks,
Jay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-29 : 11:53:22

something like this

SELECT t.username,LEFT(rl.rolelist,LEN(rl.rolelist)-1) AS RoleList 
FROM (SELECT DISTINCT userid,username FROM usertable) t
CROSS APPLY (SELECT role+',' AS [text()]
             FROM roles
             WHERE userid=t.userid
             FOR XML PATH(''))rl(rolelist)

replace with actual columns to get your solution

smithersgs
Starting Member

17 Posts

Posted - 2008-07-29 : 16:54:23

visakh16,

Thanks. My case is not that simple, but I think what youve done. Will try.

J

smithersgs
Starting Member

17 Posts

Posted - 2008-07-29 : 17:47:50

I was able to solve. Thanks for a great tip.
Jay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources