I am currently trying to simulate the group_concat
function in MySQL to MSSQL. I have followed code formats found in here and here. The problem is, when I try to execute the query, I get the message Ambiguous column name
for my column RoleID
. Here is my query:
select UserName, RoleID from tb_UserInRoles
cross apply(
select RoleName from tb_Roles
where tb_UserInRoles.RoleID = tb_Roles.RoleID
for XML Path('')
) fxMerge (RoleID) group by UserName, RoleID
I'd like to know why this particular code format present the Ambiguous column name
error message. I need to make this query work and understand it. Thanks in advance for the help.
I plan on using this in a many-to-many relationship table, wherein users of a system can have multiple roles, like this:
| User | Role |
|--------|---------|
| JamesP | Maker |
| JamesP | Approver|
| JamesP | Admin |
I want the result query to be like this:
| User | Role |
|--------|--------------------------|
| JamesP | Maker, Approver, Admin |
Try this: