String Aggregate In Oracle 10g

[matrun]

Hello there - could anyone help me with a quick question on string aggregation.
Is it possible to write some SQL on the fly (i.e. not PL/SQL) that would aggregate multiple rows into one column.
For example imagine a table called people, with columns name and city; how would I amend the query below to show in column 1 the name of a city and then in column 2 a comma-delim list of people [names] living in that city:

select name,city
from people
group by city

so instead of:

name city
jack london
sophie london
eric london
etc.

you would see

city name
london jack,sophie,eric

any ideas?

Regards

MAtt Rundle

 

[LKBrwnDBA]

Try this:

col names format a35
SELECT     city, SUBSTR (MAX (SYS_CONNECT_BY_PATH (NAME, ',')), 2) names
      FROM (SELECT city, NAME,
                   ROW_NUMBER () OVER (PARTITION BY city ORDER BY NAME) rn
              FROM people)
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND PRIOR city = city
  ORDER BY city
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[matrun]

Thanks! Fantastic. Thanks so much.