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の階層問い合わせ
