複雑な２表の結合について

質問

下記の２表があり、表AのCAL01～31の値と表BのROOM_NOが紐付いております。

＜表A＞
ID, CAL01, CAL02, CAL03, CAL04, CAL05, ・・CAL29,　CAL30, CAL31
1, 1, 1, 1, 1, 1, 1, 2, 2
2, 1, 1, 2, 2, 2, 2, 2, 3

＜表B＞
ID,ROOM_NO,ROOM_CODE,KINGAKU
1, 1, 301, 1000
1, 2, 401, 2000
2, 1, 203, 0
2, 2, 301, 1000
2, 3, 401, 2000

この場合に、ORACLE9iのSQLPLUSにて以下のような結果を返したい場合、どのようなSELECT文にしたら宜しいでしょうか？

ROOM_CODE,ID, TOTAL_KINGAKU
203, 2, 0
※2(CAL01～02の個数)×0
301, 1, 29000
※29(CAL01～29の個数)×1000
　　　　　 2, 2000
　　　　　　　　 ※2(CAL01～02の個数)×1000
401, 1, 4000
※2(CAL30～31の個数)×2000
　　　　　 2, 2000
　　　　　　　　 ※1(CAL31の個数)×2000

宜しくお願いします。

回答No.2

jamshid6 · ベストアンサー

ビューにしたわけですね。今回のビューのレイアウト（厳密にはID,DATE,ROOM_NO)でテーブルを用意し、
そこから表Ａのようなビューを作成するのが通常のデザインだと思います。

ちなみに表Ａのレイアウトだと、PL/SQLでやってもあまり楽にはならないです。
PL/SQLで別アプローチを取るとしても、CAL01からCAL31までをデリミタ付で結合した文字列から、
指定ROOM_NOの登場件数をカウントする関数を作成するくらいですね。

回答No.1

jamshid6

コメントが付かない理由は明らかで、
・「CAL01～02の個数」「CAL01～29の個数」「CAL31の個数」などの集計範囲が何によって決まっているのか
・「個数」の定義
がわからないからです。
いずれにしても表Ａのような構造（繰返し項目が存在するので非正規形です）である以上、NVL(CAL01,0)+NVL(CAL02,0)+.....+NVL(CAL29,0)のような相当ムゴいクエリになるのは不可避と思います。

お礼

コメントありがとうございます。

「個数」という表現が悪かったですね。。
それと何のための表かという説明がないのでイメージができなかったと思います。申し訳ありませんでした。

まず、表ＡのCAL01～31はカレンダーの1日から31日で、IDごとにそれぞれの日にいた部屋No.が格納されています。
それで最終結果として、部屋ごとのIDごとに合計金額を算出したいわけで、ID=1の場合ですと、CAL01～29に部屋No.=1におり、CAL30,31に部屋No.=2にいます。ID=2は、CAL01,02に部屋No.=1に、CAL03～CAL30に部屋No.=2に、CAL31に部屋No.=3にいます。

結論として、以下のような中間ビューを作成し、表Bと結合することで解決しましたが、PL/SQL等を使ってもっとスマートに書くことは可能なのでしょうか？

create or replace view VIEW_XXXX
(
ID,YYYYMMDD,ROOM_NO
) as
(select ID,YYYYMM || '01',CAL01 from 表A
union all
select ID,YYYYMM || '02',CAL02 from 表A
union all
select ID,YYYYMM || '03',CAL03 from 表A
・
・
・
);