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

aggregation, oracle, sql, string-concatenation

Solution

(WARNING - `WM_CONCAT` is an unsupported function that was removed in version 12c. Unless you're using a very old database, you should avoid this function. You should probably use `LISTAGG` instead.)

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.

Problem

I have a table that looks like this: ``` A 1 A 2 B 1 B 2 ``` And I want to produce a result set that looks like this: ``` A 1 2 B 1 2 ``` Is there a SQL statement that will do this? I am using Oracle. Related questions: - Returning multiple rows from a single row My question is close to the opposite of this question. - Use LINQ to concatenate This is exactly what I want to do, but without LINQ.

Original source

Related problems