Is There Any Function in Oracle Similar to Group_Concat in MySQL

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

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.

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.

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;

Collecting into VARRAY inside SQL statement

Duplicate of this one:

If you have Oracle 10G:

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
                  ID
            FROM
                  ACCOUNTS
            WHERE
                  OC_YEAR = INPUT_VAL )
    LOOP
        RETURN_TEXT :=
               RETURN_TEXT
            || ','
            || X.ID;
    END LOOP;

    RETURN LTRIM ( RETURN_TEXT,
                ',' );
END;
/

So, you can do like:

SELECT
      GET_COMMA_SEPARATED_VALUE ( ID ),
      OC_YEAR
FROM
      ACCOUNTS;

If you have got oracle 11g, you can use listagg :

SELECT
      LISTAGG ( OC_YEAR,
              ', ' )
      WITHIN GROUP (ORDER BY ID),
      OC_YEAR
FROM
      ACCOUNTS
GROUP BY
      OC_YEAR;

---

Related Topics