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.    
sureshrainaAsked:
Who is Participating?
 
rmacfadyenCommented:
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 = ''
    select @csl = @csl + 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

    set @csl = substring(@csl, 1, len(@csl) - 1)

    return @csl
end


Regards

Rob
0
 
rmacfadyenCommented:
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 http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21873941.html

0
 
sureshrainaAuthor Commented:
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
0
 
rmacfadyenCommented:
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
0
 
sureshrainaAuthor Commented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.