C# と VB.NET の質問掲示板

Oracleでの集計方法についての質問です。

以下の�@、�A、�Bのテーブルがそれぞれあり、その結果を集計したく調べていましたが
実際にSQLでの記述方法が分かりません。
御存じの方いらっしゃいましたら教えていただけますでしょうか？
※�@（縦）と�A（横）の名称は全て表示し、データ上存在しない場合は出力時には0表示


�@店舗コードテーブル
TEN_CODE  店名
001       A支店
002       B支店
003       C支店

�A材料コードテーブル
CODE
ZAI_CODE   材料名
1          木
2          鉄
3          プラ

�Bデータ
TEN_CODE   ZAI_CODE   SUURYOU
001        2          1
001        3          1
001        3          1
003        1          2
003        2          1

出力結果
        木   鉄   プラ
A支店    0    1     2
B支店    0    0     0
C支店    2    1     0

■No101318 (SITORO さん) に返信
> Oracleでの集計方法についての質問です。

残念ながら Oracle では、列方向に可変なクエリを作ることはできません。
代替策としては下記のいずれかで対応することになるでしょう。


（案１）下記のような SQL を動的に生成する。
→ 取得結果は望ましいものとなりますが、列情報は固定値的に記述していくしかありません。
　他にも「PIVOT」を使う手がありますが、抽出データに合わせて決め打ちになってしまうという点では一緒です。

SELECT
  "店舗コードテーブル"."店名"
, NVL(SUM(CASE "データ".ZAI_CODE WHEN '1' THEN "データ".SUURYOU END), 0) AS "木"
, NVL(SUM(CASE "データ".ZAI_CODE WHEN '2' THEN "データ".SUURYOU END), 0) AS "鉄"
, NVL(SUM(CASE "データ".ZAI_CODE WHEN '3' THEN "データ".SUURYOU END), 0) AS "プラ"
FROM
  "店舗コードテーブル" CROSS JOIN "材料コードテーブル"
  LEFT JOIN "データ"
  ON  "データ".TEN_CODE = "店舗コードテーブル".TEN_CODE
  AND "データ".ZAI_CODE = "材料コードテーブル".ZAI_CODE
GROUP BY
  "店舗コードテーブル"."店名"
ORDER BY
  "店舗コードテーブル"."店名"



（案２）Oracle 側では行方向に並べて取得し、取得後にアプリ側で整形する方針に変更。
→ 取得結果の再整形が必要になりますが、こちらは SQL をデータに合わせて動的に構築する必要がなくなります。
　※下記の場合、店舗 3 種×材料 3 種で、総計 9 レコードが取得されることになる。

SELECT
  "店舗コードテーブル".TEN_CODE, "店舗コードテーブル"."店名"
, "材料コードテーブル".ZAI_CODE, "材料コードテーブル"."材料名"
, NVL(SUM(EXAMPLE.SUURYOU), 0) AS SUURYOU
FROM
  "店舗コードテーブル" CROSS JOIN "材料コードテーブル"
  FULL JOIN (SELECT TEN_CODE, ZAI_CODE, SUM(SUURYOU) AS SUURYOU
  FROM "データ" GROUP BY TEN_CODE, ZAI_CODE) EXAMPLE
  ON  EXAMPLE.TEN_CODE = "店舗コードテーブル".TEN_CODE
  AND EXAMPLE.ZAI_CODE = "材料コードテーブル".ZAI_CODE
GROUP BY
  "店舗コードテーブル".TEN_CODE, "店舗コードテーブル"."店名"
, "材料コードテーブル".ZAI_CODE, "材料コードテーブル"."材料名"
ORDER BY
  "店舗コードテーブル".TEN_CODE
, "材料コードテーブル".ZAI_CODE


もしも全店合計や材料別合計も必要なら、CUBE を併用する
http://oracle.se-free.com/dml/06_cube.html

■No101321 (魔界の仮面弁士 さん) に返信
> （案１）下記のような SQL を動的に生成する。
> → 取得結果は望ましいものとなりますが、列情報は固定値的に記述していくしかありません。
> 　他にも「PIVOT」を使う手がありますが、抽出データに合わせて決め打ちになってしまうという点では一緒です。
案１をプロシジャで動的にビュー作成したら
決め打ちにならないのですが
ビュー作成とSELECT実行が１トランザクションで実行できないので
あまりよろしくはないですね。

--SQL*Plusでテスト
CREATE OR REPLACE PROCEDURE 集計VIEW作成
AUTHID CURRENT_USER
IS
    s VARCHAR2(500);
    CURSOR cur IS SELECT * FROM 材料 ORDER BY ZAI_CODE;
    rec cur%ROWTYPE;
BEGIN
    s := 'CREATE OR REPLACE VIEW 集計 ' ||
         'AS ' ||
         'SELECT 店名';

    OPEN cur;
    LOOP
        FETCH cur INTO rec;
        EXIT WHEN cur%NOTFOUND;
        s := s || ', ' ||
             'NVL(SUM(CASE データ.ZAI_CODE WHEN ''' || rec.ZAI_CODE || ''' THEN データ.SUURYOU END), 0) ' || rec.材料名;
    END LOOP;
    CLOSE cur;

    s := s || ' ' ||
           'FROM 店舗, データ ' ||
           'WHERE 店舗.TEN_CODE = データ.TEN_CODE (+) ' ||
           'GROUP BY 店名';

    EXECUTE IMMEDIATE s;
END;
/

EXEC 集計VIEW作成;

SELECT * from 集計 ORDER BY 1;

店名           木         鉄       プラ
------ ---------- ---------- ----------
A支店           0          1          2
B支店           0          0          0
C支店           2          1          0