MySQL's group_concat Equivalents in PostgreSQL, Oracle, DB2, HSQLDB, and SQLite

The GROUP_CONCAT() function in MySQL

MySQL has a very handy function which concatenates strings from a group into one string. For example, let's take a look at the children table with data about parents' and children's names.

To get the names of children of each person as a comma-separated string, you use the GROUP_CONCAT() function as follows:

SELECT parent_name, group_concat(child_name)
FROM children
GROUP BY parent_name;

The result:

+-------------+--------------------------+
| parent_name | group_concat(child_name) |
+-------------+--------------------------+
| John        | Tom,Anna                 |
| Michael     | Sylvie,Sophie            |
+-------------+--------------------------+

To make sure the names of the children in each string are alphabetically ordered and to use semicolon ";" as a separator, use this query:

SELECT parent_name,
       group_concat(child_name ORDER BY child_name asc separator ';')
FROM children
GROUP BY parent_name;

The result:

+-------------+--------------------------+
| parent_name | group_concat(child_name) |
+-------------+--------------------------+
| John        | Anna;Tom                 |
| Michael     | Sophie;Sylvie            |
+-------------+--------------------------+

In most other databases, there is no GROUP_CONCAT() function. Let's take a look at how you can aggregate strings in a group in other databases.

GROUP_CONCAT() in PostgreSQL

In PostgreSQL you use ARRAY_TO_STRING() and ARRAY_AGG() functions:

SELECT parent_name, ARRAY_TO_STRING(ARRAY_AGG(child_name), ',')
FROM children
GROUP BY parent_name;

The function ARRAY_AGG() returns an array of elements in the group, the function ARRAY_TO_STRING() concatenates the elemens in the array using the given separator.

If you want to order the names in each group:

SELECT parent_name,
ARRAY_TO_STRING(ARRAY_AGG(child_name ORDER BY child_name ASC), ',')
FROM children
GROUP BY parent_name;

Prior to version 8.4, you would have had to define the function ARRAY_AGG before using it:

CREATE AGGREGATE ARRAY_AGG (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

From version 9.0 on, you can use the string_agg function:

SELECT parent_name, string_agg(child_name, ',')
FROM children
GROUP BY parent_name;

group_concat in Oracle

In Oracle from version 11g you can use the LISTAGG function.

SELECT parent_name, 
       LISTAGG(child_name, ',') WITHIN GROUP (ORDER BY child_name)
FROM children
GROUP BY parent_name;

Note that the WITHIN GROUP (ORDER BY ...) clause is part of the LISTAGG function syntax so there's no way to concat strings without specifying the order.

Prior to version 11g, StackOverflow recommends that you use the 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;

And then use the query:

SELECT parent_name, get_comma_separated_value(child_name) 
FROM children;

I haven't tested it though.

group_concat in DB2

In DB2 there is no handy function which could be used, so you have to use the WITH query:

WITH
    children_numbered(rowNum, parent_name, child_name) AS
    ( 
      SELECT rownumber() over(partition by parent_name),
      parent_name, child_name
      FROM children 
    ),
    children_grouped (parent_name, list, idx) AS
    ( 
      SELECT parent_name, child_name, 1
      FROM children_numbered
      WHERE rowNum = 1
      UNION ALL
      SELECT 
       children_grouped.parent_name, 
       children_grouped.list || ', ' || children_numbered.child_name, 
       children_grouped.idx + 1
      FROM children_grouped, children_numbered
      WHERE 
       children_grouped.parent_name = children_numbered.parent_name 
       AND children_grouped.idx + 1 = children_numbered.rowNum
    )
SELECT parent_name, list
FROM children_grouped
WHERE ( parent_name, idx ) IN (
    SELECT parent_name, MAX(rowNum)
    FROM children_numbered
    GROUP BY parent_name )
ORDER BY 1

First you number each person's children (subquery children_numbered). Then, in the subquery children_grouped, you append each child's name one by one, separating the next name with a comma. Finally, you select rows where all children's names have been appended.

Modify the query appropriately, if you need a specific ordering or separator.

group_concat in HSQLDB

HSQLDB syntax is quite similar to MySQL:

SELECT parent_name, group_concat(child_name)
FROM children
GROUP BY parent_name;

or with the order and separator:

SELECT parent_name, 
  group_concat(child_name ORDER BY child_name SEPARATOR ';')
FROM children
GROUP BY parent_name;

group_concat in SQLite

The syntax in SQLite is similar to MySQL:

SELECT parent_name, group_concat(child_name)
FROM children
GROUP BY parent_name;

SELECT parent_name, group_concat(child_name, ';')
FROM children
GROUP BY parent_name;

However, you cannot specify the order using SQLite's group_concat function. To get the order, you have to use a subquery:

SELECT parent_name, group_concat(child_name)
FROM (
  SELECT parent_name, child_name
  FROM children
  ORDER BY child_name
  )
GROUP BY parent_name;