sql-server - 用分隔符连接某一列的同时对行进行分组

Question

我必须在连接某个列时以某种方式对行进行分组，我不确定我将如何进行。以下是我需要的示例。

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

从此表中正常选择将产生以下结果:

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

我需要的是以下内容:

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

我不确定这是否可行，但如果有人知道如何去做，那将非常有帮助。提前致谢。

Answer 1

这是一个难题。要深入了解它，请参阅这篇出色的文章:

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

对于一个简单的解决方案，我使用了一个游标，它完成了这项工作:

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