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.
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
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 ;
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 ;
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?
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 :)