【SQL】【Oracle】 複数結合、自己結合したテーブルから同じカラムのデータ（中身別）を複数列として抽出したい。

Question

前提・実現したいこと

　お疲れ様です。

まずタイトルがうまく要約できず、わかりにくいものとなってしまって申し訳ございません。
もしかするとそもそも不可能であるかもしれないのですが、可能である場合はご教授いただきたいです。
実現したいことは以下です。

■人情報が入っているテーブルからその人名と、その人が休暇を取得した場合の休暇行使日を、取得した休暇別に取得したいです。

・人情報が入っているテーブル＝「PERSON」のカラムはそれぞれ、
person_id int primary key
person_code varchar2(10) not null
person_name varchar2(50) not null
で、入っているデータは仮に、

person_id	person_code	person_name
1	001	A
2	002	B
3	003	C

とします。

・休暇の取得情報が入っているテーブル＝「HOLIDAY」のカラムはそれぞれ、
holiday_id int primary key
person_code varchar2(10) not null
holiday_code varchar2(4) not null
holiday_name varchar2(30) not null
used_date char(10 char)
で、入っているデータは仮に、

|holiday_id|person_code|holiday_code|holiday_name|used_date|
|:--:|:--:|:--:|:--:|
|1|001|1000|代休|2020/10/01|
|1|001|2000|有給|2020/10/02|
|1|001|1000|代休|2020/10/03|
|2|002|2000|有給|2020/10/04|
|2|002|1000|代休|2020/10/05|
|3|003|3000|怪我病欠|2020/10/06|
|3|003|3000|怪我病欠|2020/10/07|
|3|003|3000|怪我病欠|2020/10/08|

とします。

これら二つのテーブルを、取得したい休暇情報分自己結合したのち、以下のようなイメージでデータを取得したいと考えております。

person_name	代休	有休	怪我病欠
A	2020/10/01	null	null
A	null	2020/10/02	null
A	2020/10/03	null	null
B	null	2020/10/04	null
B	2020/10/05	null	null
C	null	null	2020/10/06
C	null	null	2020/10/07
C	null	null	2020/10/08

試したこと

以下のようにしてみたのですが当然すべてに合致するものしか取得できずでして、
どのようにしたら上記のような形でデータを取得できるかわかりません。
もし方法があるようでしたらご教授願えるとありがたいです。

SQL（Oracle）

SELECT 
 p.person_name, 
 h1.used_date 代休,  
 h2.used_date 有給,  
 h3.used_date 怪我病欠
FROM 
 PERSON p
JOIN
 HOLIDAY h1
ON
 p.person_code = h1.person_code
JOIN
 HOLIDAY h2
ON
 p.person_code = h2.person_code
JOIN
 HOLIDAY h1
ON
 p.person_code = h3.person_code
WHERE
 h1.holiday_code = '1000'
AND
 h2.holiday_code = '2000'
AND
 h3.holiday_code = '3000'

文章が拙いものとなってしまい、もしかすると伝わりづらいかもしれませんが、何卒よろしくお願い申し上げます。

以上です。

Comments

[gentaro]

回答者に確認してほしいならテーブル作成とデータ挿入のSQLを質問文に書いておいた方が良いです。

[maru-omochi]

ご指摘ありがとうございます。
こちらのご指摘に気が付く前に回答いただきまして解決してしまいました。
次から質問する際にはご指摘内容を踏まえて投稿するように心がけます。
わざわざ拙い投稿を見ていただき、かつご指摘までしていただき感謝です。
お時間を頂戴いたしましてありがとうございました。

また質問した際にはお力添え、ご教授よろしくお願いいたします。

Answer 1

結合後のwhere条件にすると、Null考慮も必要になりますから、先に集合を絞るように、結合時に条件指定します。

SELECT p.person_name
     , h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠
FROM  PERSON p
      left JOIN HOLIDAY h1
      ON    h1.holiday_code='1000'
        and p.person_code = h1.person_code
      left JOIN HOLIDAY h2
      ON    h2.holiday_code='2000'
        and p.person_code = h2.person_code
      left JOIN HOLIDAY h3
      ON    h3.holiday_code='3000'
        and p.person_code = h3.person_code

上記をイメージし易いような別な記述にすると、

SELECT p.person_name
     , h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠
FROM  PERSON p
      left JOIN (
        select * from HOLIDAY where holiday_code='1000'
      ) h1
      ON    p.person_code = h1.person_code
      left JOIN (
        select * from HOLIDAY where holiday_code='2000'
      ) h2
      ON    p.person_code = h2.person_code
      left JOIN (
        select * from HOLIDAY where holiday_code='3000'
      ) h3
      ON    p.person_code = h3.person_code

where条件で指定しようとすると、

SELECT p.person_name
     , h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠
FROM  PERSON p
      left JOIN HOLIDAY h1
      ON    p.person_code = h1.person_code
      left JOIN HOLIDAY h2
      ON    p.person_code = h2.person_code
      left JOIN HOLIDAY h3
      ON    p.person_code = h3.person_code
where (h1.holiday_code='1000' or h1.holiday_code is null)
  and (h2.holiday_code='2000' or h2.holiday_code is null)
  and (h3.holiday_code='3000' or h3.holiday_code is null)

Comments

[maru-omochi]

早速の回答ありがとうございます。
凄いです！！思った通りに実行できました。感動しております。
迅速に対応していただいてありがとうございます。本当に感謝です。

[sazi]

まあ、希望の形ではあるんですけど、単にカラムを振り分けただけですから、間を詰めた「クロス集計にしたい」みたいな要望に変わりそうな気はしますが。

[maru-omochi]

クロス集計　というものがあるのですね。
ありがとうございます。早速調べてみることにします。

そもそも回答いただいたような、結合する際にあのように条件を指定する方法があることすら知らず、またたどり着くことができずにいたくらいでして、この内容もしっかり学習しなおして消化したいと思います。
小一時間調べていたのですが、こんなにあっさり回答いただきまして本当に感謝です。

またなにかわからないことに直面した際はお力添えしていただけるとありがたいです。
この度は本当にありがとうございました。

[sazi]

追記しておきました。頑張って下さい。

[maru-omochi]

ほんとになにからなにまでありがとうございます！感謝です。

[maru-omochi]

お疲れ様です。
こちらに記述することではないとは思うのですが、質問として再掲するのもまた申し訳ないと思いこちらにて質問させてください。

上の例で、取得休暇情報テーブル（HOLIDAY)に、
holiday_id 　person_code 　holiday_code 　holiday_name 　used_date
　　1 　　　　　　001　　　　　 1000 　　　　　　　有給 　　2020/10/02

というデータがもう一つ入った状態で集計をかけると、
person_name 　　　代休 　　　　　有休 　　　怪我病欠
A 　　　　　　　2020/10/01 　　　2020/10/02 　null
A 　　　　　　　2020/10/02 　　　2020/10/02 　null
A 　　　　　　　2020/10/03 　　　　null 　　　null

のように有休列に同じ日付のデータが取れてしまうのですが、
これを避け、二番目の行の有休がnull となるためには例のクロス集計というものを使えば実現可能でしょうか。

また、最初の行のセレクト文は、システムから動的に項目を抽出される仕様となっており、
そちらに集計関数が使えないという場合にこの問題を解決する方法はありますでしょうか。
※SELECT p.person_name , h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠の部分。

結合するテーブルを、サブクエリで(SELECT ROW_NUMBER () OVER(PARTITION BY person_name,holiday_code ORDER BY used_date) rowNum のように集計し、それを結合して出すということを試みたのですが、この方法ですと結合条件のholiday_codeに対するデータがない場合、またはholiday_code自体が存在しない場合に正しく集計することができませんでした。

もし実現可能でしたら、実現するために必要な知識を得るためにどのように検索をネットでかけたらよいか教えていただけますでしょうか。。。

[sazi]

コメントした通り詰めるという要望になりましたね。

それぞれの項目で重複なく上に詰めて表示するという事ですよね。
その為には、それぞれを何行目に表示するのかとう情報を作り出して結合する必要があります。

なかなかピンポイントで参考になる情報を見つけるのは厳しいのでないかと思います。
なので、要件を整理して、新たに質問された方が良いかと。

[maru-omochi]

返信ありがとうございます。
承知いたしました。クロス集計というものを使うというのだけでもわかっただけ感謝です。
もうすこし自力で頑張ってみますが、どうにもならないようでしたらこちらでご協力求めるかもしれません。
その時はお手数ですがお手隙の際に回答いただけるとありがたいです。
いつもありがとうございます。

[sazi]

要望が明確では無いですけど、結合する際には、それぞれの行数を求めるだけではなく、それぞれの行数の最大となる行を作成してそこから結合という形になると思います。
頑張ってください。