Challenge:
I have these 3 simple tables
1) Users:
UserID INT,
Username VARCHAR(32)
2) UserInRoles:
UserID INT,
RoleID INT
3) Roles:
RoleID INT,
RoleName VARCHAR(32)
So if I joinining these 3 tables I might end up with for instance:
UserID, Username; RoleName:
1; ‘test’; ‘Administrators’
1; ‘test’; ‘Mobile Users’
2; ‘test1′; ‘Administrators’
As you can see the UserID 1 has 2 roles
I want to display in my grids as follow:
UserID, Username; RoleName:
1; ‘test’; ‘Administrators, Mobile Users’
2; ‘test1′; ‘Administrators’
The query that I use is this:
SELECT UserID
Username,
STUFF((SELECT ‘, ‘ + CAST(rl.RoleName AS VARCHAR(64)) AS RoleName
FROM Roles rl
INNER JOIN UsersInRoles uir ON rl.RoleID = uir.RoleID
WHERE UserID = u.UserID
FOR XML PATH(”)),1,1,”) AS RoleDescription
FROM Users
The data returned for the RoleDescription is returning in XML format such as:
AdministratorsMobile Users instead of ‘Administrators, Mobile Users.’ ?!?!
Solution:
Remove the RoleName on after the CAST
SELECT UserID
Username,
STUFF((SELECT ‘, ‘ + CAST(rl.RoleName AS VARCHAR(64))
FROM Roles rl
INNER JOIN UsersInRoles uir ON rl.RoleID = uir.RoleID
WHERE UserID = u.UserID
FOR XML PATH(”)),1,0,”) AS RoleDescription
FROM Users
This will return as per normal which is: ‘Adminitrators, Mobile Users’
Katy 3:07 pm on 24/06/2009 Permalink
Pretty good post. I just came across your site and wanted to say
that I have really enjoyed reading your posts. Anyway
I’ll be subscribing to your feed and I hope you write again soon!