Agregate rows in Oracle SQL statement

Question

Hi,

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
...

Answer 1

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

Answer 2

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

Use like this