March 31, 2005 at 5:35 pm
Hi,
I have two tables, 1 defines a group and then the other holds the members of the group (Members). What I want to do is when I query the table to get a list of the groups, I want a comma separated list of the members names.
Something like this,
Group 1 - Developers - Matt, Mike, Tom, Paul
The Group information will come from my group table and then the members names come from the users table with a third table that links the members to the groups.
Is there somehow I can do this in a Query?
Thanks and Regards,
Matt
March 31, 2005 at 6:35 pm
I believe the only set based way to do this is through the use of a function that performs in essence a subselect. Outside of a function I think you have to do a cursor into a temp table and then select out the temp table as your results. Following is code for the function and how to leverage it:
Create Function ListMembers ( @Group varchar(100) )
returns varchar(1000)
begin
declare @Members varchar(1000)
set @Members = ''
SELECT @Members = case when @Members = '' then '' else ', ' end + MemberName
FROM GroupMembers
WHERE GroupName = @Group
ORDER BY MemberName
return @Members
end
SELECT GroupName, dbo.ListMembers(GroupName)
FROM Groups
ORDER BY GroupName
March 31, 2005 at 7:39 pm
Hi,
Thanks for your reply, it has given me a great start but I havenot managed to get it to work 100% yet.
I modified the query as follows:
ALTER Function ListMembers
(
@SuperLexiconID int
)
returns varchar(1000)
begin
declare @Members varchar(1000)
set @Members = ''
SELECT @Members = case when @Members = '' then '' else ', ' end + FirstName + ' ' + LastName
FROM LexiconUserDatabases LUD
LEFT OUTER JOIN Lexicons L ON LUD.LexiconID = L.LexiconID
LEFT OUTER JOIN Users U ON L.UserID = U.UserID
WHERE LUD.SuperLexiconID = @SuperLexiconID
ORDER BY U.FirstName
return @Members
end
and when execute it, I only get back one record (In Query Analyzer). If I run this query, I get back 4 so where have I gone wrong?
SELECT U.FirstName + ' ' + U.LastName
FROM LexiconUserDatabases LUD
LEFT OUTER JOIN Lexicons L ON LUD.LexiconID = L.LexiconID
LEFT OUTER JOIN Users U ON L.UserID = U.UserID
WHERE LUD.SuperLexiconID = 2
Regards,
Matt
March 31, 2005 at 7:46 pm
Sorry. I forgot a string concatenation.
change:
SELECT @Members = case when @Members = '' then '' else ', ' end + FirstName + ' ' + LastName
to:
SELECT @Members = @Members + case when @Members = '' then '' else ', ' end + FirstName + ' ' + LastName
March 31, 2005 at 8:10 pm
Hi Aaron,
Thanks for the help! Something that I found is that when I had the order by clause it would only return 1 name. As soon as I removed this order by clause it all worked 100%
Thanks and Regards,
Matt
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply