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.

rmacfadyen

You could do the comma separated list with a function and then the select just becomes:

select
dbo.dbcritic_user.id, dbo.dbcritic_user.user_name, dbo.UserRolesCsl(dbo.dbcritic_user.id)
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.zrole_id
where dbo.dbcritic_user.zuser_id = @UserId
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

sureshraina

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

rmacfadyen

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

sureshraina

ASKER

Thanks for your response.

I tried this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER function [dbo].[dbcritic_UserRolesCsl](@UserId 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.zrole_id
where dbo.dbcritic_user_role.zuser_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)

ASKER CERTIFIED SOLUTION

rmacfadyen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial