2010年6月6日日曜日

OracleでGroup By時にCSV形式で文字連結

 OracleでGroup Byした際、文字列もカンマ区切りで一行に入れて返す方法を見つけました。
ユーザー定義関数を作ったり面倒そうなのは嫌なのでSQLだけで出来る単純な方法です。

結果
GROUP_KEY  STRING
---------- ----------------------------------
Group 1    a
Group 2    a,b
Group 3    a,b,c
Group 4    a,b
Group 5    a,b,d,e
Group 8    a,b

コード
SELECT
  GROUP_KEY ,
  LTRIM
    ( EXTRACT
      ( XMLAGG
        ( XMLELEMENT( "V", ',' || VAL )
        ORDER BY VAL
      ),
      '/V/text()'
    ) ,
    ','
  ) AS STRING
FROM
  ( SELECT DISTINCT
      GROUP_KEY, VAL        -- PRODUCES DISTINCT VALUES
    FROM   T
    WHERE  VAL IS NOT NULL  -- FILTERS OUR NULL VALUES
  ) TB
GROUP BY
  GROUP_KEY

データ
GROUP_KEY  VAL
---------- ----------
Group 1    a
Group 2    a
Group 2    b
Group 3    a
Group 3    b
Group 3    c
Group 4    a
Group 4    a
Group 4    b
Group 4    b
Group 5    a
Group 5    b
Group 5    d
Group 5    e
Group 5    (null)
Group 6    (null)
Group 7    (null)
Group 7    (null)
Group 8    a
Group 8    a
Group 8    b
Group 8    b
Group 8    (null)
Group 8    (null)

参考にしたのは以下のサイトです。
http://www.sqlsnippets.com/en/topic-11785.html