oracle sql 1つのフィールドに文字列としてデータが登録されてしまっている場合のSELECT文について

以下のようなテーブルがあります。
表1は年度と型番をで部品IDを一意に特定できます
表2は年度と部品IDで部品名称を一意に特定できます
しかし表1の作りがよくなく
部品IDの1つのカラムにスペース区切りで文字列部品IDをまとめて登録しています。
2019年度の製品「AB01」部品ID「A1」部品名称「ヒューズ」
2019年度の製品「AB02」部品ID「A2」部品名称「ボルト」
2020年度の製品「AB01」部品ID「A1」部品名称「改良ヒューズ」

このようなSQL結果を得たいのですが方法が思いつきません。
表1の部品IDを分割して表1をきれいにする方法がいいと思ったのですが
なにかいい案はないでしょうか
部品は年度と部品で引っ張られるので型番が異なっても
同じ部品を使用しています。

表1の部品IDには30個以上の文字列がスペース区切りで連結されています。
レコードはどちらも1万以上
なのでfor文を回す必要があると思うのですが。。。。
全然思いつきません。

案だけでもいいのでご回答お待ちしております。

データ構造例

表1
年度｜製品型番｜部品ID
2019 AB01 　　　 A1 A2 A3 A4 A5 　←文字列
2019 AB02 　　　A1 A2 A3 A4 A5
2020 AB01 　　　A1 A2 A3 B1 B2
2020 AB02 A1 A2 A3 B1 B2

表2
年度｜部品ID ｜部品名称
2019 A1 ヒューズ
2019 A2 　　ボルト
.
.
2020 A1 　　改良ヒューズ

Orlofsky

2021/02/18 12:36

[DB2]タグは必要ですか？OracleとDB2はかなり違いますから、不要なら削ってください。

行動規範の内容に同意します

回答3件

表1が以下のように定義されているとします。

SQL
1CREATE TABLE t (
2  year INT,
3  model VARCHAR(100),
4  id VARCHAR(100)
5);
6
7INSERT INTO t VALUES 
8  (2019, 'AB01', 'A1'),
9  (2019, 'AB02', 'A1 A2'),
10  (2020, 'AB01', 'A1 A2 A3'),
11  (2020, 'AB02', 'A1 A2 A3 A4');

Oracleのバージョンにもよりますが、共通表式による再帰SQLがサポートされているなら、たとえば以下のようなSQLで正規化することはできます。

SQL
1WITH RECURSIVE r (
2  year,
3  model, 
4  depth,
5  pos,
6  id,
7  rest
8) AS (
9    SELECT 
10      year,
11      model,
12      0,
13      INSTR(id, ' '),
14      id,
15      id
16    FROM t
17  UNION ALL
18    SELECT
19      year,
20      model,
21      depth + 1,
22      INSTR(rest, ' '),
23      CASE WHEN pos = 0 THEN rest ELSE SUBSTR(rest, 1, pos - 1) END,
24      CASE WHEN pos = 0 THEN '' ELSE SUBSTR(rest, pos + 1) END
25    FROM r
26    WHERE rest <> ''
27)
28SELECT
29  year,
30  model, 
31  id
32FROM r
33WHERE depth > 0

この実行結果は以下のようになるはずです。あとは表2とJOINなどすればよいかと思います。

year	model	id
2019	AB01	A1
2019	AB02	A1
2019	AB02	A2
2020	AB01	A1
2020	AB01	A2
2020	AB01	A3
2020	AB02	A1
2020	AB02	A2
2020	AB02	A3
2020	AB02	A4

投稿2021/02/19 01:45

neko_the_shadow

総合スコア2349

表1の部品IDを分割して表1をきれいにする方法がいいと思ったのですが、なにかいい案はないでしょうか

部品IDを区切られた空白で分割しそれぞれをunionすれば良さそうです。
oracleにはsplit()のような分割して取り出すような関数はないので、自作関数などを流用して使えば楽できます。
文字列からトークンを切り出す
上記を使用すると、

SQL
1select 年度, 製品型番 , strtoken(部品ID, ' ', 1) 部品ID from 表1 where strtoken(部品ID, ' ', 1) is not null
2union all 
3select 年度, 製品型番 , strtoken(部品ID, ' ', 2) from 表1 where strtoken(部品ID, ' ', 2) is not null
4union all 
5 ・
6 ・