Aggregate Rows in Oracle SQL Expression

Question

I have a table of items stored this way:

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

And I need to get SQL Query:

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

+2

kilikopela Apr 19 10 at 12:14

2 answers

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

Use it like

-2

Karthik Apr 19 10 at 12:22

Peter lang · Accepted Answer · 2010-04-19T12:18:07+0000

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 you to sort your values, and it already ships with Oracle:

A1  B1, B2, B3
A2  B1, B4

Otherwise, you can use stragg

Tom Kyte's feature described in Lines per Line .

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

returns

A1  B1,B3,B2
A2  B1,B4