Group up rows while concatenating a certain column with a delimeter

Question

I have to somehow group up rows while concatenating a certain column, I am not sure how I would go about doing this. The following is an example of what I need.

CREATE TABLE People(
PersonName varchar(100),
PersonAge int
)

INSERT INTO People
SELECT 'bill', 21

INSERT INTO People
SELECT 'harry', 21

INSERT INTO People
SELECT 'wesley', 21

INSERT INTO People
SELECT 'tom', 42

INSERT INTO People
SELECT 'paul', 42

INSERT INTO People
SELECT 'phil', 53

a normal select from this table will produce the following:

bill    21
harry   21
wesley  21
tom     42
paul    42
phil    53

what I need is the following:

bill, harry, wesley    21
tom,paul               42
phil                   53

I am not sure if this is possible but it would be really helpful if anyone know how to do it. Thanks in advance.

Answer 1

This is a difficult problem. For an in depth treatment of it see this excellent post:

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

For a simple solution, I used a cursor, it does the job:

create procedure
doIt
as

create table #out
(people varchar(2000) null, -- assumed max length of concatenated string
age int)

insert into #out(age)
select distinct personAge from people

declare @str varchar(2000)
select @str = isnull(@str,'') + personname +',' from people

declare @age int
declare cur cursor for select age from #out

open cur
fetch next from cur into @age

while @@fetch_status =0
begin 
    set @str = ''
    select @str = isnull(@str,'') + personname +',' from people where personage = @age
    update #out set people = left(@str,len(@str)-1) where age=@age
    fetch next from cur into @age 
end

close cur
deallocate cur

select * from #out

Answer 2

Here is a way without cursors using the link that tekBlues put in his post:

WITH CTE ( PersonAge, person_list, person_name, length )
AS 
( 
SELECT PersonAge, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0
FROM People
GROUP BY PersonAge
UNION ALL
SELECT p.PersonAge, 
    CAST( person_list + CASE WHEN length = 0 THEN '' ELSE ', ' END + PersonName AS VARCHAR(8000) ),
    CAST( PersonName AS VARCHAR(8000)), length + 1
FROM CTE c
INNER JOIN People p ON c.PersonAge = p.PersonAge
WHERE p.PersonName > c.person_name 
)
SELECT PersonAge, person_list
FROM 
( 
SELECT PersonAge, person_list, RANK() OVER ( PARTITION BY PersonAge ORDER BY length DESC )
FROM CTE 
) D ( PersonAge, person_list, rank )
WHERE rank = 1 ;

Answer 3

This is a hard problem in Sql Server. You could, for example, create a custom string concatenation aggregate. But that requires you to load a .NET assembly into your Sql Server; not all DBA's would agree to that.

On the client side, this is really easy. Why don't you look into solving this client side?

Answer 4

SELECT p1.personage, ( SELECT personName + ',' FROM people p2 WHERE p2.personage = p1.personage ORDER BY personName FOR XML PATH('') ) AS Results FROM people p1 GROUP BY personage ;

Answer 5

If you're using MySQL, it is as simple as it could be

SELECT GROUP_CONCAT(PersonName SEPARATOR ', ') as name, PersonAge 
FROM People GROUP BY PersonAge

There's also ORDER and DISTINCT options :)