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

今回の題材について

今回の例では、以下のような縦持ちのテーブル構造を考える。

帳票管理テーブル

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：求める結果はどの単位でグルーピングされているかを考える。

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

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

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：横持ちの項目を作成する。

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

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

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

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

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

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

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

その3：横持ちの表示内容を修正する。

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

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	社員	帳票	作成日(招待状)	確認日(招待状)	作成日(アンケート)	確認日(アンケート)
1	Aさん	招待状	04月10日	04月07日
2	Aさん	アンケート			05月01日	05月04日
3	Bさん	招待状	10月30日	10月31日

あと、少しである。

その4：システムが求める形でグルーピングする。

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

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日

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

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 テーブル 
     ) グルーピング社員と帳票