omachizura

SQLの基本的なことからスタートし、高度な検索・集計方法などの応用テクニックまでできるスキルを紹介します。


グループ化したデータをカンマ区切りで取得する

グループ化した際に集計する関数といえば、MAX,MIN,COUNTなどがよく使われると思います。

文字列のカラムを集計した時にMAX,MINなどで一つの値を取得するのではなく、グループ対象のすべてのデータをカンマ区切りなどでつなげて取得したい場合があるかと思います。以下のような取得です。(わかりやすくするため、正規化は考えていません)

社員マスタ
社員コード社員名部署
1テスト社員1開発部
2テスト社員2営業部
3テスト社員3開発部
4テスト社員4営業部

部署ごとに社員をカンマ区切りで取得したい。

社員マスタ
部署社員一覧
開発部テスト社員1,テスト社員3
営業部テスト社員2,テスト社員4

LISTAGG関数

oracle 11g R2ではMAX,MINと同じようにLISTAGGという関数が用意されています。oracle 11g R2以前のバージョンでの取得方法は後ほど紹介します。

以下のように使います。

SELECT
  T1.部署
  LISTAGG(T1.社員名, ',') WITHIN GROUP (ORDER BY T1.社員コード) 社員一覧
FROM
  社員マスタ T1
GROUP by
  T1.部署

LISTAGG(T1.社員名, ',')でグループ対象の社員名を,区切りで取得します。第2引数の,で区切り文字を指定します、/などで区切ることもあるかと思います。省略時は,になります。

WITHIN GROUP (ORDER BY T1.社員コード)ORDER BY T1.社員コードで区切りで連結する順番を指定できます。上の例では社員コードの昇順でソートするため、テスト社員1,テスト社員3とソートされます。ORDER BY T1.社員コード DESCとした場合はテスト社員3,テスト社員1となります。

wmsys.wm_concat

oracle 11g R2以前のバージョンでLISTAGGが使えない場合はwmsys.wm_concatという関数を使います。ただし、この方法はマニュアルにも載っていない方法なので、サポート対象外ですのでご注意ください。

SELECT
  T1.部署
  wmsys.wm_concat(T1.社員名) 社員一覧
FROM
  社員マスタ T1
GROUP by
  T1.部署




4
次へ

4
次へ