Agregate rows in Oracle SQL statement

Question

I have a table of items stored this way :

A1 | B1
A1 | B2
A1 | B3
A2 | B1
A2 | B4
...

And I need to retrieve with a SQL Query :

A1 | B1, B2, B3
A2 | B1, B4
...

Duplicate of this question: http://stackoverflow.com/questions/492563/oracle-combine-multiple-results-in-a-subquery-into-a-single-comma-separated-valu — Doug Porter, Apr 19 '10 at 12:42

Peter Lang · Accepted Answer · 2010-04-19T12:48:08.983

3

If you have 11g Release 2 you can use Listagg:

Select a, Listagg(b, ', ') Within Group ( Order By b )
From t
Group By a

It allows to sort your values, and it already comes with Oracle:

A1  B1, B2, B3
A2  B1, B4

Otherwise you can use the stragg function by Tom Kyte, described in Rows to String.

Select a, stragg(b)
From t
Group By a

returns

A1  B1,B3,B2
A2  B1,B4

edited Apr 19 '10 at 12:48

answered Apr 19 '10 at 12:18

Peter Lang

1

Peter, if the OP was on 11gR2 could they use LISTAGG? – Ian Carpenter Apr 19 '10 at 12:41
Unfortunately, I do not have 11g – kilikopela Apr 19 '10 at 14:01

score -2 · Answer 2 · answered Apr 19 '10 at 12:22

-2

SELECT *
FROM `table1`
ORDER BY `afield` ASC

Use like this

answered Apr 19 '10 at 12:22

Karthik

2

Sorry, but I fail to see how this would help? – Peter Lang Apr 19 '10 at 12:24

2 Answers2