カラム内にカンマ区切りで格納されているデータを分割するSQL

さて、今回はOracleでカラム内にカンマ区切りで格納されているデータをレコードごとに分割するSQLをご紹介します。（LISTAGGの逆ですね）

テーブルはこんな感じ

コード	値
1	リンゴ,レモン,トマト
2	サイ,イヌ,トラ

以下、SQLです。

WITH A AS (
    SELECT
        code
       ,value
    FROM hoge
)
, B AS (
    SELECT
        A.code
       ,REGEXP_SUBSTR(A.value,'[^,]+', 1, lines.column_value) AS split_value
    FROM A
        ,TABLE( CAST(MULTISET( SELECT LEVEL FROM dual
            CONNECT BY REGEXP_SUBSTR(A.value, '[^,]+', 1, LEVEL) IS NOT NULL) AS sys.odciNumberList)) lines
)
SELECT * FROM B
ORDER BY code

ざっくばらんに解説すると、正規表現でカンマを抜きだし、階層問い合わせを使って抜き出した順に順序を割り振りしていきます。
そして、その割り振ったカンマの順序から文字列を切り取ります。

また、この程度の内容ではWITH句は必要ないですが、本来はこの後に処理を続けていくことを考えると、WITH句を使っていた方が見やすくなって良いと思います。

結果はこんな感じです。（column_valueの内容も載せておきます）

コード	値	column_value
1	リンゴ	1
1	レモン	2
1	トマト	3
2	サイ	1
2	イヌ	2
2	トラ	3

以上、備忘録がてらのSQL紹介でした。

テーブルの内容の法則性はわかる人にはわかりますよね。