Oracle SQL でカンマ区切り値を行に効率的に変換するにはどうすればよいですか?

Oracle SQL のカンマ区切り値変換戦略

データベース レコード内のデータをカンマ区切り値として整理すると、表形式で表現する際に課題が生じます。この記事では、データの抽出と操作を容易にするために、このようなデータを効率的に行に変換するためのいくつかの SQL テクニックについて説明します。

問題: データベース レコード内のカンマ区切り値

次のテーブル構造を考えてみましょう。値列にはカンマ区切りの値が含まれています。

<code>CREATE TABLE tbl1 (
  id NUMBER,
  value VARCHAR2(50)
);

INSERT INTO tbl1 VALUES (1, 'AA, UT, BT, SK, SX');
INSERT INTO tbl1 VALUES (2, 'AA, UT, SX');
INSERT INTO tbl1 VALUES (3, 'UT, SK, SX, ZF');</code>

目標は、このデータを表形式に変換し、各値を独自の行に分割することです。

<code>ID | VALUE
-------------
1  | AA
1  | UT
1  | BT
1  | SK
1  | SX
2  | AA
2  | UT
2  | SX
3  | UT
3  | SK
3  | SX
3  | ZF</code>

方法 1: REGEXP_SUBSTR を使用して接続します

1 つの方法は、Oracle の REGEXP_SUBSTR および CONNECT BY 句を利用することです。

<code>SELECT DISTINCT id, TRIM(REGEXP_SUBSTR(value, '[^,]+', 1, level) ) VALUE, LEVEL
FROM tbl1
CONNECT BY REGEXP_SUBSTR(value, '[^,]+', 1, LEVEL) IS NOT NULL
ORDER BY id, LEVEL;</code>

このメソッドは正規表現 (REGEXP_SUBSTR) を使用して各部分文字列を抽出し、CONNECT BY はカンマ区切りの値を再帰的に繰り返します。

方法 2: 再帰結合を使用した CTE

もう 1 つの手法は、再帰結合で共通テーブル式 (CTE) を使用することです。

<code>WITH t (id, res, val, lev) AS (
  SELECT id, TRIM(REGEXP_SUBSTR(value, '[^,]+', 1, 1)) RES, VALUE AS VAL, 1 AS LEV
  FROM tbl1
  WHERE REGEXP_SUBSTR(VALUE, '[^,]+', 1, 1) IS NOT NULL
  UNION ALL
  SELECT id, TRIM(REGEXP_SUBSTR(VAL, '[^,]+', 1, LEV + 1)) RES, VAL, LEV + 1 AS LEV
  FROM t
  WHERE REGEXP_SUBSTR(VAL, '[^,]+', 1, LEV + 1) IS NOT NULL
)
SELECT id, res, lev
FROM t
ORDER BY id, lev;</code>

このメソッドは再帰的 CTE を使用して、カンマ区切りの値を個々の要素に分割します。

方法 3: INSTR の再帰的メソッドを使用する

3 番目の解決策は、INSTR 関数で再帰的 CTE を使用して、各部分文字列の開始位置と終了位置を識別します。

<code>WITH t (id, value, start_pos, end_pos) AS
  (SELECT id, VALUE, 1, INSTR(VALUE, ',')
  FROM tbl1
  UNION ALL
  SELECT id,
    VALUE,
    end_pos                    + 1,
    INSTR(VALUE, ',', end_pos + 1)
  FROM t
  WHERE end_pos > 0
  )
SELECT id,
  SUBSTR(VALUE, start_pos, DECODE(end_pos, 0, LENGTH(VALUE) + 1, end_pos) - start_pos) AS VALUE
FROM t
ORDER BY id,
  start_pos;</code>

このメソッドは INSTR を利用して各部分文字列の位置を再帰的に決定し、それに応じて部分文字列を抽出します。

以上がOracle SQL でカンマ区切り値を行に効率的に変換するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。