[解決済み] PostgreSQL の 'group by' クエリで、文字列フィールドの文字列を連結する方法は？

質問

group byクエリ内のフィールドの文字列を連結する方法を探しています。例えば、あるテーブルがあるとします。

ID   COMPANY_ID   EMPLOYEE
1    1            Anna
2    1            Bill
3    2            Carol
4    2            Dave

で、company_idでグループ化して、こんな感じにしたかったんです。

COMPANY_ID   EMPLOYEE
1            Anna, Bill
2            Carol, Dave

mySQLには、これを行うための組み込み関数があります。 グループ_コンキャット

解決方法は？

PostgreSQL 9.0以降をご使用ください。

最近のPostgres（2010年以降）は string_agg(expression, delimiter) という関数は、質問者が求めていることを正確に実行します。

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

また、Postgres 9では、Postgres 9のインストール後に ORDER BY 節 を任意の集約式で使用することができます。 そうでない場合は、すべての結果を順番に並べるか、未定義の順番で処理しなければなりません。というわけで、こう書けるようになりました。

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

PostgreSQL 8.4.x:

PostgreSQL 8.4 (2009年)導入。 集約関数 array_agg(expression) で、値を配列に集めます。次に array_to_string() を使うことで、目的の結果を得ることができます。

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x およびそれ以前のバージョン。

この質問が最初に出されたとき、文字列を連結するための組み込みの集約関数はありませんでした。最も単純なカスタム実装 ( このメーリングリストの投稿でVajda Gaboが提案した を使用することです。 textcat 関数 (これは || 演算子)を使用します。

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

以下は CREATE AGGREGATE のドキュメントをご覧ください。

これは単純にすべての文字列をセパレータなしでくっつけているだけです。最後に ", " を入れずに間に挟むには、自分で連結関数を作って、上の "textcat" の代わりにするとよいかもしれません。以下は、私が8.3.12でテストしたものです。

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

このバージョンでは、行の値がNULLや空でもカンマを出力するので、次のような出力になります。

a, b, c, , e, , g

余分なカンマを削除して出力する場合は、このようにします。

a, b, c, e, g

次に ELSIF のようにチェックを入れてください。

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;