sureshraina
asked on
Comma or semi-colon delimated values
Hello,
I have the query below using joins from 3 diff tables
SELECT u.id, u.user_name, role.role
FROM dbo.dbcritic_user AS u LEFT OUTER JOIN
dbo.dbcritic_user_role AS r ON u.id = r.zuser_id LEFT OUTER JOIN
dbo.dbcritic_role AS role ON r.zrole_id = role.id
The result of this query looks something like this:
-------------------------- ---------- ---------- ---------- ---------- ---
1 john admin
1 john Manager
1 john Bookie
2 Tom Bookie
3 Kris User
4 Cathy Bookie
5 Raff User
5 Raff Bookie
What I mean is a user can have more than 1 role. Is there a way instead of showing multiple records show something like this below
1 John Admin;Manager;Bookie
5 Raff User;Bookie
2 Tom Bookie
Please help.
I have the query below using joins from 3 diff tables
SELECT u.id, u.user_name, role.role
FROM dbo.dbcritic_user AS u LEFT OUTER JOIN
dbo.dbcritic_user_role AS r ON u.id = r.zuser_id LEFT OUTER JOIN
dbo.dbcritic_role AS role ON r.zrole_id = role.id
The result of this query looks something like this:
--------------------------
1 john admin
1 john Manager
1 john Bookie
2 Tom Bookie
3 Kris User
4 Cathy Bookie
5 Raff User
5 Raff Bookie
What I mean is a user can have more than 1 role. Is there a way instead of showing multiple records show something like this below
1 John Admin;Manager;Bookie
5 Raff User;Bookie
2 Tom Bookie
Please help.
ASKER
Hello,
Thanks a lot for this. I have a problem though the function is working fine. I have a extra comma at the begininig. How to avoid that one.
Thanks again
Thanks a lot for this. I have a problem though the function is working fine. I have a extra comma at the begininig. How to avoid that one.
Thanks again
Did you change the set @csl = null bit? By setting @csl to null then the for the first record the coalesce will use '', for the 2nd through nth record it will use @csl + ','.
A bit tricky for my taste... but it does work well.
Another choice would be to use @csl = @csl + dbo.dbcritic_role.role + ',' and then after the select do a set @csl = substring(@csl, 1, len(@csl) - 1). I think this is a bit more clear... but not by much :)
Rob
A bit tricky for my taste... but it does work well.
Another choice would be to use @csl = @csl + dbo.dbcritic_role.role + ',' and then after the select do a set @csl = substring(@csl, 1, len(@csl) - 1). I think this is a bit more clear... but not by much :)
Rob
ASKER
Thanks for your response.
I tried this
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[dbcritic_UserRolesC sl](@UserI d as int)
returns varchar(8000)
as begin
declare @csl varchar(8000)
set @csl = null
select @csl = @csl + dbo.dbcritic_role.role + ',' + dbo.dbcritic_role.role
from dbo.dbcritic_user_role
inner join dbo.dbcritic_role on dbo.dbcritic_role.id = dbo.dbcritic_user_role.zro le_id
where dbo.dbcritic_user_role.zus er_id = @UserId
order by dbo.dbcritic_role.role
@csl = substring(@csl, 1, len(@csl) - 1)
return @csl
end
It is giving error saying incorrect syntax near @csl(substring)
I tried this
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER function [dbo].[dbcritic_UserRolesC
returns varchar(8000)
as begin
declare @csl varchar(8000)
set @csl = null
select @csl = @csl + dbo.dbcritic_role.role + ',' + dbo.dbcritic_role.role
from dbo.dbcritic_user_role
inner join dbo.dbcritic_role on dbo.dbcritic_role.id = dbo.dbcritic_user_role.zro
where dbo.dbcritic_user_role.zus
order by dbo.dbcritic_role.role
@csl = substring(@csl, 1, len(@csl) - 1)
return @csl
end
It is giving error saying incorrect syntax near @csl(substring)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select
dbo.dbcritic_user.id, dbo.dbcritic_user.user_nam
from dbo.dbcritic_user
The function looks like:
create function dbo.UserRolesCsl(@UserId as int)
returns varchar(8000)
as begin
declare @csl varchar(8000)
set @csl = null
select @csl = coalesce (@csl + ',', '') + dbo.dbcritic_role.role
from dbo.dbcritic_user_role
inner dbo.dbcritic_role on dbo.dbcritic_role.id = dbo.dbcritic_user_role.zro
where dbo.dbcritic_user.zuser_id
order by dbo.dbcritic_role.role
return @csl
end
Regards,
Rob
ps. The CSL function for this solution came from mikelittlewood's post https://www.experts-exchange.com/questions/21873941/Follow-on-question.html