DBの縦持ちを横持ちに変換するアイデアについて

###今回の題材について
今回の例では、以下のような縦持ちのテーブル構造を考える。

####帳票管理テーブル
```sql
SELECT 社員, 帳票, 帳票作成日, 帳票確認日 FROM テーブル;
```
|No|社員|帳票|帳票作成日|帳票確認日|
|:-:|:-:|:-:|:-:|:-:|
|1|Aさん|招待状|04月01日|04月07日|
|2|Aさん|招待状|04月08日||
|3|Aさん|招待状|04月10日||
|4|Aさん|アンケート|05月01日|05月04日|
|5|Bさん|招待状|10月01日|10月15日|
|6|Bさん|招待状|10月30日|10月31日|

上記のテーブルをシステムでは以下のように表示する。
SQLのみで実現した際に、どのように考えたのかをまとめる。

####システムからの表示
|No|社員|作成日(招待状)|確認日(招待状)|作成日(アンケート)|確認日(アンケート)|
|:-:|:-:|:-:|:-:|:-:|:-:|
|1|Aさん|04月10日|04月07日|05月01日|05月04日|
|2|Bさん|10月30日|10月31日|||

- 社員でグルーピングする。
- 招待状とアンケートは横持ちで表示する。
- 各々の作成日と確認日には直近（＝最大）の日付を格納する。

上記の例だと、Aさんは最新の招待状を確認していないことになる。

##元ネタのテーブルレコードの説明
####No.1~3
Aさんに対する招待状が作成されたことを表している。
04月01日～04月10日にかけて、計3回も帳票を作成している。これは、データが更新されたために、帳票を再作成していることを表す。

####No.4
Aに対して、アンケートという、招待状とは別の帳票を作成していることを表す。

####No.5,6
Bさんに対して招待状が2回作成されている。アンケートは作成されていない。



##考え方
####その1：求める結果はどの単位でグルーピングされているかを考える。
システムで求める結果をみると、社員単位でグルーピングされていることがわかる。
だが、素直に社員のみでグルーピングしてはいけない。それは、横持ち項目で帳票ごとに直近の日付を求める必要があるからだ。
仮に、帳票単位でグルーピングをしなかった場合、招待状とアンケートの作成日と確認日を判別することは不可能になる。

よって、社員と帳票でグルーピングをする。
グルーピングをすることで、帳票作成日と帳票確認日に対して集合関数が適用できる。

```sql
SELECT 社員, 帳票, MAX(帳票作成日), MAX(帳票確認日) FROM テーブル GROUP BY 社員, 帳票
```

|No|社員|帳票|帳票作成日(直近)|帳票確認日(直近)|
|:-:|:-:|:-:|:-:|:-:|
|1|Aさん|招待状|04月10日|04月07日|
|2|Aさん|アンケート|05月01日|05月04日|
|3|Bさん|招待状|10月30日|10月31日|

####その2：横持ちの項目を作成する。
横持ちで表示させる項目は「作成日(招待状)」,「確認日(招待状)」,「作成日(アンケート)」,「確認日(アンケート)」である。

```sql
SELECT 社員,
       帳票,
       '' as '作成日(招待状)',
       '' as '確認日(招待状)',
       '' as '作成日(アンケート)',
       '' as '確認日(アンケート)'
FROM テーブル 
GROUP BY 社員, 帳票
```

4つの受け皿には何を格納したらよいか。とりあえず、最大値を表示しておく。

```sql
SELECT 
       社員, 
       帳票,
       MAX(帳票作成日) as '作成日(招待状)',
       MAX(帳票確認日) as '確認日(招待状)',
       MAX(帳票作成日) as '作成日(アンケート)',
       MAX(帳票確認日) as '確認日(アンケート)'
FROM テーブル 
GROUP BY 社員, 帳票
```

|No|社員|帳票|作成日(招待状)|確認日(招待状)|作成日(アンケート)|確認日(アンケート)|
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
|1|Aさん|招待状|04月10日|04月07日|<font color="Red">04月10日</font>|<font color="Red">04月07日</font>|
|2|Aさん|アンケート|<font color="Red">05月01日</font>|<font color="Red">05月04日</font>|05月01日|05月04日|
|3|Bさん|招待状|10月30日|10月31日|<font color="Red">10月30日</font>|<font color="Red">10月31日</font>|

帳票作成日と確認日を2回ずつ繰り返しているだけなので、招待状の日付がアンケートの項目に表示される。（アンケートも同じ。）
赤文字の表示は正しくないが、一旦保留。

この表は、帳票別にレコードが生成されているため、今だ縦持ちである。
しかし、表示内容が正確ではないにしろ、横持ちの項目を表示することができた。
縦持ちと横持ちの混在とでも表現すべきであろうか。

####その3：横持ちの表示内容を修正する。
前述したとおり、横持ち項目の表示が正しくない。
case文を使用して、招待状の日付が、招待状の列だけに表示されるようにする。（アンケートも同じ。）
帳票列も削除してよいだろう。

```sql
SELECT 
       社員, 
       --帳票,
       CASE WHEN 帳票 = '招待状'     THEN MAX(帳票作成日) END as '作成日(招待状)',
       CASE WHEN 帳票 = '招待状'     THEN MAX(帳票確認日) END as '確認日(招待状)',
       CASE WHEN 帳票 = 'アンケート' THEN MAX(帳票作成日) END as '作成日(アンケート)',
       CASE WHEN 帳票 = 'アンケート' THEN MAX(帳票確認日) END as '確認日(アンケート)'
FROM テーブル 
GROUP BY 社員, 帳票
```
|No|社員|<font color="LightGray">~~帳票~~<font>|作成日(招待状)|確認日(招待状)|作成日(アンケート)|確認日(アンケート)|
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
|1|Aさん|<font color="LightGray">~~招待状~~<font>|04月10日|04月07日|||
|2|Aさん|<font color="LightGray">~~アンケート~~<font>|||05月01日|05月04日|
|3|Bさん|<font color="LightGray">~~招待状~~<font>|10月30日|10月31日|||

あと、少しである。

####その4：システムが求める形でグルーピングする。
システムが求める表にするためには、社員のみでグルーピングをする必要がある。
その1では、帳票ごとの日付を求めるため、帳票を含めてグルーピングしていた。その理由は、帳票ごとに直近の日付を求める必要があったからである。しかし、その3で、帳票ごとに表示する列を振り分けることができたた。
すなわち、その3で求めた表は社員でグルーピングしても、招待状とアンケートの日付が混同することはない。

```sql
SELECT 
       グルーピング社員と帳票.社員,
       MAX([作成日(招待状)])     as '作成日(招待状)',
       MAX([確認日(招待状)])     as '確認日(招待状)',
       MAX([作成日(アンケート)]) as '作成日(アンケート)',
       MAX([確認日(アンケート)]) as '確認日(アンケート)'
FROM (
       SELECT
              社員, 
              CASE WHEN 帳票 = '招待状'     THEN MAX(帳票作成日) END as '作成日(招待状)',
              CASE WHEN 帳票 = '招待状'     THEN MAX(帳票確認日) END as '確認日(招待状)',
              CASE WHEN 帳票 = 'アンケート' THEN MAX(帳票作成日) END as '作成日(アンケート)',
              CASE WHEN 帳票 = 'アンケート' THEN MAX(帳票確認日) END as '確認日(アンケート)'
       FROM テーブル 
       GROUP BY 社員, 帳票
     ) グルーピング社員と帳票
GROUP BY グルーピング社員と帳票.社員
```

結果(再掲)

|No|社員|作成日(招待状)|確認日(招待状)|作成日(アンケート)|確認日(アンケート)|
|:-:|:-:|:-:|:-:|:-:|:-:|
|1|Aさん|04月10日|04月07日|05月01日|05月04日|
|2|Bさん|10月30日|10月31日|||


###おまけ：ウィンドウ関数バージョン

```sql
SELECT DISTINCT
       社員,
       MAX([作成日(招待状)]) OVER(PARTITON BY 社員),
       MAX([確認日(招待状)]) OVER(PARTITON BY 社員),
       MAX([作成日(アンケート)]) OVER(PARTITON BY 社員),
       MAX([確認日(アンケート)]) OVER(PARTITON BY 社員)
FROM (
       SELECT DISTINCT
         社員,
         CASE WHEN 帳票 = '招待状'     THEN MAX(帳票作成日) OVER(PARTITON BY 社員, 帳票) END as '作成日(招待状)',
         CASE WHEN 帳票 = '招待状' THEN MAX(帳票作成日) OVER(PARTITON BY 社員, 帳票) END as '確認日(招待状)',
         CASE WHEN 帳票 = 'アンケート'  THEN MAX(帳票作成日) OVER(PARTITON BY 社員, 帳票) END as '作成日(アンケート)',
         CASE WHEN 帳票 = 'アンケート' THEN MAX(帳票作成日) OVER(PARTITON BY 社員, 帳票) END as '確認日(アンケート)'
       FROM テーブル 
     ) グルーピング社員と帳票
```