问题描述
我有 3 个表,分别代表Users"、Roles"和多对多的UsersInRoles"——键为:UserId、RoleId;相关列:UserName、RoleName
I've got 3 tables representing "Users", "Roles", and the many-to-many "UsersInRoles" - with keys: UserId, RoleId; pertinant columns: UserName, RoleName
在 ***** html 应用程序中,我想显示所有用户及其所在角色的列表.我试图从 SQL 构建一个将返回此信息的查询.应该对角色列表进行分隔,以便我可以进行适当的演示操作(取决于演示平台,例如用 BR 标记替换分隔符).
In the ***** html app, I want to show a list of all users and the roles they are in. From SQL, I'm trying to construct a single query that will return this information. The list of roles should be delimited so I can do the appropriate presentation manipulation (depending on presentation platform, like replace delimiter with BR tag).
对用户列表使用一个选择,然后为每个用户单独选择来获取角色是很简单的,但是尝试构建一个输出以下内容的选择让我感到困惑.
Using one select for the list of users and then individual selects for each user to get the roles is straight-forward, but trying to construct a single select that outputs the below has got me stumped.
UserId UserName Roles ------ -------- ----- 1 user1 *****,Guest,PowerUser 2 user2 Guest 3 user3 4 user4 PowerUser,Guest
提前致谢,
--埃德
Thanks in advance,
--Ed
--编辑--
现在使用以下查询(感谢所有人,特别是 Raymund & 他的博客):
WITH RolesList AS ( SELECT u.UserName, ( SELECT r.RoleName + ',' AS 'data()' FROM Roles r JOIN UsersInRoles ur ON ur.RoleId = r.RoleId WHERE ur.UserId = u.UserId FOR XML PATH('') ) AS RolesCSV FROM Users u ) SELECT UserName, LEFT(RolesCSV, LEN(RolesCSV)-1) AS RolesCSV FROM RolesList
推荐答案
这是您的解决方案:
转换/将行解析为 SQL 中的分隔字符串列
编辑
如果您需要进一步澄清,这里是答案
If you need further clarity here is the answer
WITH UserList as ( SELECT UserID, UserName, (SELECT RoleName + ',' AS 'data()' FROM Roles INNER JOIN UsersInRoles ON Roles.RoleID = UsersInRoles.RoleID WHERE UsersInRoles.UserID = Users.UserID FOR XML PATH('')) AS RoleCSV FROM Users ) SELECT UserID, UserName, LEFT(RoleCSV, LEN(RoleCSV)-1) as RoleCSV from UserList