Group 化 する際に 文字列を カンマ区切りで結合する方法を 2つ 紹介します。
ストアドファンクション
ストアドファンクション を使って文字列を結合します。
1 2 3 4 5 6 7 8 9 10 11 |
CREATE FUNCTION MEMBER_NAMES (I_GROUP_ID IN NUMBER) RETURN VARCHAR; IS TEXT VARCHAR := ''; BEGIN FOR CUR IN (SELECT NAME FROM SAMPLE_TABLE WHERE GROUP_ID = I_GROUP_ID) TEXT := ", " || CUR.NAME; END LOOP; RETURN LTRIM(TEXT, ', '); END; |
このようなファンクションを作っておいて、 次のような SQL を実行します。
1 2 3 4 |
SELECT GROUP_ID, CONCAT_FUNCTION(GROUP_ID) AS MEMBER_NAMES FROM SAMPLE_TABLE GROUP BY GROUP_ID; |
GROUP_ID | MEMBER_NAMES |
---|---|
1 | A, B, C |
2 | D, E |
もし、 MEMBER_NAMES
の出力結果が文字列として長くなりすぎる場合は、 文字数の長さを調べて分岐処理を行うように ファンクション を変更すれば OK です。 末尾に “等” をつけたりもできます。 区切り文字を変更することもできます。
ファンクション内で使われている LTRAM(PARAM1, PARAM2)
は、 PARAM1
の左端に、 PARAM2
があれば削除する関数です。 右側を削除する場合は RTRAM
が使えます。
wm_concat
wm_concat
という関数を使っても同じことができます。 Oracle のマニュアルにも載っていない関数なので、 オラクルのサポートは受けられないと思います。
1 |
SELECT wm_concat(ENAME) FROM EMP GROUP BY JOB; |
上の SQL を実行すると、 AAAAA, BBBBB, CCCCC, DDDDD
というような結果が出力されます。
データと期待する出力結果をわかりやすくテーブルの形にして書くと、下のようになります。
ENAME | JOB |
---|---|
AAAAA | A |
BBBBB | A |
CCCCC | A |
DDDDD | A |
wm_concat(ENAME) |
---|
AAAAA, BBBBB, CCCCC, DDDDD |
同等の機能をもつ ListAgg 関数 が Oracle 11g R2 で 正式にサポートされました。
参考: Oracleの階層問い合わせ