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: stackoverflow.com/questions/492563/… — Doug Porter
– Doug Porter, Commented Apr 19, 2010 at 12:42

Peter Lang · Accepted Answer · 2010-04-19 12:48:08Z

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

answered Apr 19, 2010 at 12:18

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter, if the OP was on 11gR2 could they use LISTAGG?

Unfortunately, I do not have 11g

Karthik · Accepted Answer · 2010-04-19 12:22:23Z

-2

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

Use like this

answered Apr 19, 2010 at 12:22

Karthik

3,3415 gold badges31 silver badges38 bronze badges

Sorry, but I fail to see how this would help?