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
Casting Scientific Notation (From Varchar -> Numeric) in a View
Oracle SQL - Sum and Group Data by Week
Number of Fridays Between Two Dates
SQL Server 2008 Express Concat() Doesn't Exist
The Argument 1 of the Xml Data Type Method "Value" Must Be a String Literal
Ignore Certain Columns When Using Bulk Insert
Concatenate One Field After Group By
This Select Query Takes 180 Seconds to Finish
Convert Row Value in to Column in SQL Server (Pivot)
Active Directory Data into SQL Table
How to Transform Rows to Columns
Best to Use * When Calling a Lot of Fields in MySQL
How to Find the Total Number of Used Days in a Month
List of Stored Procedure from Table