?
Solved

Comma or semi-colon delimated  values

Posted on 2006-06-04
5
Medium Priority
?
297 Views
Last Modified: 2012-06-21
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.    
0
Comment
Question by:sureshraina
  • 3
  • 2
5 Comments
 
LVL 5

Expert Comment

by:rmacfadyen
ID: 16829397
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
 

Author Comment

by:sureshraina
ID: 16835908
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
 
LVL 5

Expert Comment

by:rmacfadyen
ID: 16836530
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
 

Author Comment

by:sureshraina
ID: 16836923
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
 
LVL 5

Accepted Solution

by:
rmacfadyen earned 2000 total points
ID: 16838234
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question