emulate group_concat in MSSQL, ambiguous column name?

Question

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  |

Saharsh Shah · Accepted Answer · 2013-12-16 06:08:21Z

6

Try this:

SELECT UIR.UserName, MAX(STUFF(fxMerge.RoleID, 1, 1, '')) 
FROM tb_UserInRoles UIR
CROSS APPLY(
    SELECT ',' + RoleName 
    FROM tb_UserInRoles UIR1 
    INNER JOIN tb_Roles RM ON UIR1.RoleID = RM.RoleID
    WHERE UIR.UserName = UIR1.UserName 
    FOR XML PATH('')
) fxMerge (RoleID) 
GROUP BY UIR.UserName

edited Dec 16, 2013 at 6:08

answered Dec 16, 2013 at 5:48

Saharsh Shah

29.1k9 gold badges52 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Saharsh Shah Over a year ago

@JamesP Check my updated answer. It will resolve your problem. I had joined on the USERNAME column because I don't know the table structure. So you have to join on USERID column if you have. Change in query is WHERE UIR.UserId = UIR1.UserId instead of UIR.UserName = UIR1.UserName

JamesP Over a year ago

it works brilliantly now :) If you don't mind, I'd like to know which does what in the code, like, why did you use MAX and STUFF, why use INNER JOIN etc.

Saharsh Shah Over a year ago

@JamesP Subquery returns all the role name for particular user so I had to join both tables. the subquery returns ,Maker,Approver,Admin for JampesP user. So I need ro remove first comman. So I have use STUFF function to implement this

Saharsh Shah Over a year ago

@JamesP Inner query not subquery.

Collectives™ on Stack Overflow

emulate group_concat in MSSQL, ambiguous column name?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related