Is there an Oracle SQL query that aggregates multiple rows into one row? [duplicate]

It depends on the version of Oracle you're using. If it supports the wm_concat() function, then you can simply do something like this:

SELECT field1, wm_concat(field2) FROM YourTable GROUP BY field2;

wm_concat() basically works just like group_concat() in MySQL. It may not be documented, so fire up ye olde sqlplus and see if it's there.

If it isn't there, then you'll want to implement something equivalent yourself. You can find some instructions on how to do this in the string aggregation page at oracle-base.com.

Pretty old topic, but it could help others since Oracle improved in the mean time.

The LISTAGG function is what you are looking for (in 11g at least)

In Oracle 10g+:

SELECT  *FROM    (        SELECT  *        FROM    mytable        MODEL        PARTITION BY                (grouper)        DIMENSION BY                (ROW_NUMBER() OVER (PARTITION BY grouper ORDER BY id) AS rn)        MEASURES                (val, val AS group_concat, 0 AS mark)        RULES SEQUENTIAL ORDER (                group_concat[rn > 1] ORDER BY rn = group_concat[CV() - 1] || ', ' || val[CV()],                mark[ANY] ORDER BY rn = PRESENTV(mark[CV() + 1], 0, 1)                )        )WHERE   mark = 1ORDER BY        grouper

See this article in my blog for explanations:

• GROUP_CONCAT in Oracle 10g