How to Write a Query That Does Something Similar to MySQL's Group_Concat in Oracle

How to write a query that does something similar to MySQL's GROUP_CONCAT in Oracle?

SELECT p.pkt_nazwa AS name, wm_concat(u.us_nazwa) AS services
FROM punkty p
JOIN kategorie_uslug ku ON ku.pkt_id = p.pktk_1_id
JOIN usluga u ON u.usk_1_id = ku.us_id
GROUP BY p.pkt_nazwa
ORDER BY p.pkt_nazwa;

wm_concat() function is present in most versions.

More about string aggregating in Oracle.

Is there any function in oracle similar to group_concat in mysql?

11g and higher: Use listagg:

SELECT 
col1,
LISTAGG(col2, ', ') WITHIN GROUP (ORDER BY col2) "names"
FROM table_x
GROUP BY col1

10g and lower: One method is to use a function:

CREATE OR REPLACE FUNCTION get_comma_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 LTRIM(return_text, ',');
END;
/

To use the function:

select col1, get_comma_separated_value(col1) from table_name

Note: There is an (unsupported) function WM_CONCAT available on certain older versions of Oracle, which might help you out - see here for details.

In MySQL:

SELECT col1, GROUP_CONCAT(col2) FROM table_name GROUP BY col1

Group_concat MySQL function's equivalent in Oracle

You can use Tom Kyte's STRAGG function

Equivalent function to STUFF in SQL (GROUP_CONCAT in MySSQL / LISTAGG in Oracle)

It looks like you are in luck - Firebird 2.1 introduced a LIST() aggregate function which works like GROUP_CONCAT in MySQL, which allows a query like so:

SELECT p.Name, LIST(c.Name, ', ')
FROM parent p INNER JOIN child c on c.parentid = p.parentid
GROUP by p.Name;

Edit, re Ordering

You may be able to influence ordering by pre-ordering the data in a derived table, prior to applying the LIST aggregation function, like so:

SELECT x.ParentName, LIST(x.ChildName, ', ')
FROM
(
SELECT p.Name as ParentName, c.Name as ChildName
FROM parent p INNER JOIN child c on c.parentid = p.parentid
ORDER BY c.Name DESC
) x
GROUP by x.ParentName;

Aggregate function in MySQL - list (like LISTAGG in Oracle)

You're looking for GROUP_CONCAT()

Try this:

select group_concat(MyString separator ', ') as myList from table
where id < 4

Of course, you can group by the results.

How to Merge fields after query, i know CONCAT, but not this way

Here you go:

MySQL using group_concat:

select a.name,
a.opcode,
group_concat(month order by b.pk separator ', ') as months
from tablea a
join tableb b on a.opcode = b.opcode
group by a.name, a.opcode;

Oracle using listagg:

select a.name,
a.opcode,
listagg(month,', ') within group (order by b.pk) as months
from tablea a
join tableb b on a.opcode = b.opcode
group by a.name, a.opcode;

SQL Server using for xml path and stuff:

select a.*,
stuff((
select ', ' + month from tableb b
where a.opcode = b.opcode
order by pk
for xml path(''), type
).value('(./text())[1]', 'varchar(max)')
, 1, 2, '') as months
from tablea a;


Related Topics



Leave a reply



Submit