Is there an Oracle SQL query that aggregates multiple rows into one row?
Solution 1
(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.
Solution 2
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)
Solution 3
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 = 1
ORDER BY
grouper
See this article in my blog for explanations:
Solution 4
Try something like :
SELECT
field1,
RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", field2) ORDER BY field2), '<x>'), '</x>', ' ')) AS field2s
FROM yourTable
GROUP BY field1
Freely inspired by an answer found in this Oracle forum.
EDIT: this solution proved very resources intensive with requests involving something like 105 rows. I ended up replacing this by custom aggregate functions as suggested by John.
Solution 5
If you have got 10g, then you have to go through the function below:
CREATE OR REPLACE FUNCTION get_separated_value (input_val in number)
RETURN VARCHAR2
IS
return_text VARCHAR2(10000) := NULL;
BEGIN
FOR x IN (SELECT col2 FROM table_name WHERE col1 = input_val) LOOP
return_text := return_text || ' ' || x.col2 ;
END LOOP;
RETURN return_text;
END;
/
So, you can do like:
select col1, get_separated_value(col1) from table_name
If you have got oracle 11g, you can use listagg :
SELECT
age,
LISTAGG(name, ' ') WITHIN GROUP (ORDER BY name) "names"
FROM table_x
GROUP BY age
Ashish Dhariwal
Updated on December 14, 2021Comments
-
Ashish Dhariwal over 2 years
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.