October 2, 2005 at 11:25 pm
suppose i have a table like so
table1
======
id (int autoincrement)
names (varchar 50)
groupname (varchar 50)
values:
id names groupname
== ===== =========
1 name1 group1
2 name2 group2
3 name3 group1
and i wanted to return all names on the same group as a single string like so
result (names, group name):
name1,name3/group1
name2,group2
in mysql/postgres theres this aggregation function named GROUP_CONCAT which does the trick, is there an existing function that does the same?
slow down when you need to hurry, stop when you need to move on,
look back when you need to forget, or you might slip and leave sanity
October 3, 2005 at 10:16 am
No built in function but you can create your own.
But I'd have to say if your table has repeating group names then your table design has alot to be desired.
create table table1([id] int identity,
names varchar (50),
groupname varchar (50))
insert into table1(Names, GroupName)
select 'name1', 'group1'union
select 'name2', 'group2'union
select 'name3', 'group1'
create function concatname(@GroupName varchar(50))
Returns varchar(250)
as
BEGIN
Declare @String varchar(250)
Select @String = Coalesce(@String,'') + Names + ','
from table1
where groupName = @GroupName
set @String = left(@String, len(@String) -1)
Return @String
END
GO
select GroupName, dbo.concatname(GroupName)
from Table1
Group by GroupName
October 3, 2005 at 8:19 pm
tnx Ray_M, ill try that out
slow down when you need to hurry, stop when you need to move on,
look back when you need to forget, or you might slip and leave sanity
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply