CASE式・結合JOIN・統合UNIONで同じ結果を表示させる

CASE式と集合関数

日常生活で使う言葉としての「結合」と「統合」はほぼ同義語ですが、SQLの場合は結合はJOIN、統合はUNIONを使い、表示結果が異なります。
CostReport

CASEでどの列に値をセットするか選択して、他の列が歯抜けにならないように集合関数でGROUP化します。

CASE式ではELSEで条件に合致しない場合も0をセットしてしまうので、AとBが共に0の場合は合致する品目自体が存在しないため、「AとBのどちらかが0でない場合」という抽出条件を追加します。

SELECT
  ITM_CD,
  A,
  B
FROM
  (SELECT
    ITM_CD,
    SUM(CASE ROW_NO
    WHEN 6 THEN AMOUNT
    WHEN 7 THEN AMOUNT
    WHEN 12 THEN AMOUNT
    ELSE 0
    END) AS A,
    SUM(CASE ROW_NO
    WHEN 18 THEN AMOUNT
    WHEN 19 THEN AMOUNT
    ELSE 0
    END) AS B
  FROM
    A_COST_ITEM_TEMP

  GROUP BY
    ITM_CD
  )  
WHERE
  A <> 0
OR
  B <> 0
;

完全外部結合

CASE式と集合関数で縦並びテーブルを横並び化するということは、列ごとのSELECT結果を完全外部結合FULL OUTER JOINしているのと同じことです。

SELECT 
  a.ITM_CD, 
  b.ITM_CD, 
  nvl(a.Tot1,0), 
  nvl(b.Tot2,0) 
FROM 
  (SELECT 
    ITM_CD, 
    SUM(nvl(AMOUNT,0)) as Tot1 
  FROM 
    A_COST_ITEM_TEMP 
  WHERE 
    (ROW_NO = 6 
  OR 
    ROW_NO = 7 
  OR 
    ROW_NO = 12) 
  GROUP BY 
    ITM_CD) a 
  FULL OUTER JOIN 
  (SELECT 
    ITM_CD, 
    SUM(nvl(AMOUNT,0)) as Tot2 
  FROM 
    A_COST_ITEM_TEMP 
  WHERE 
    (ROW_NO = 18 
  OR 
    ROW_NO = 19) 
  GROUP BY 
    ITM_CD) b 
  ON 
    a.ITM_CD = b.itm_cd;

通常は「左テーブルを優先して右テーブルの主キー(プライマリーキー)と合致することを結合条件として左テーブルに列を追加することを目的とする」左外部結合LEFT (OUTER) JOINしか使う機会はありませんが、「右テーブルを優先して左テーブルの主キーと合致することを結合条件として右テーブルに列を追加することを目的とする」右外部結合RIGHT (OUTER) JOINもあります。

そして左右の外部結合のコンビネーションとして「左右の主キーを合致してもしなくても結合する完全外部結合FULL OUTER JOINがあり、今回のように「左右両方のビューのどちらかに1個でも主キーがあれば結合する」という場合に使用します。

統合(和集合)

「左右両方のビューのどちらかに1個でも主キーがあれば結合して縦並びレコードを横並び」するということは「主キー以外の情報列の並び順が合致しない左右のビューを統合UNION ALLして主キーでグループ化する」ことと同じです。

SELECT 
  A.ITM_CD, 
  SUM(T1.A), 
  SUM(T1.B)
FROM
  (SELECT 
    ITM_CD, 
    SUM(nvl(AMOUNT,0)) as A, 
    SUM(nvl(0,0)) as dummy1 
  FROM 
    A_COST_ITEM_TEMP 
  WHERE 
    (ROW_NO = 6 
  OR 
    ROW_NO = 7 
  OR 
    ROW_NO = 12) 
  GROUP BY 
    ITM_CD
  UNION ALL
  SELECT 
    ITM_CD, 
    SUM(nvl(0,0)) as dummy2, 
    SUM(nvl(AMOUNT,0)) as B
  FROM 
    A_COST_ITEM_TEMP 
  WHERE 
    (ROW_NO = 18 
  OR 
    ROW_NO = 19) 
  GROUP BY 
    ITM_CD) T1
GROUP BY 
  ITM_CD;

統合UNIONの場合、左テーブルの列名を優先して同じ並び順かつデータ型が同じ右テーブルを統合しますので、左右のビューにダミー列を追加して両方の情報列を取得できるようにしますが、主キーが重複したレコードも取得するためにUNION ALLを使用します。