SQL文でクロス集計をしたい

質問

アクセスでクロス集計のSQL文で作りたいと思っています。

このようなデータがあります
年月 支払先 数量 金額
201301 A 10 1000
201302 A 5 500
201302 B 20 2000
201301 C 10 3000
201303 C 15 1500

このデータを下記のように表示したいのですが、
分からなくて困っています。

支払先　　データ　　　201301 201302 201303
A 数量　　　　　10　　　　　 5
A 金額 1000 500
B 数量　　　　　 20
B 金額 2000
C 数量　　　　　10 15
C 金額 3000 1500
合計 数量 20 25 15
合計 金額 4000 2500 1500
どのようにすれば実現出来ますか？

今は、
SELECT 支払先,
"数量" AS データ,
Sum(IIf([年月]='201301',[数量],0)) AS 201301,
Sum(IIf([年月]='201302',[数量],0)) AS 201302,
Sum(IIf([年月]='201303',[数量],0)) AS 201303,
FROM T_データ
GROUP BY 支払先;
のクロス集計を
数量と金額、数量の合計と金額の合計でクロス集計をしたクエリを
UNIONで実現しています。
出来れば数量と金額は1つのクエリで実現したいと思いますが方法があれば教えて頂きたい
と思います。

どうぞ、よろしくお願い致します。

回答No.2

hatena1989 · ベストアンサー

別テーブルを使わずにとなると、ユオニンクエリを使うことになります。

先に、ユニオンクエリで、数量、金額、数量合計、金額合計 を縦に連結して、これをサブクエリとしてFROM句に入れて、クロス集計クエリにします。

TRANSFORM Sum(値)
SELECT 支払先, データ
FROM
(SELECT T_支払.年月, T_支払.支払先,"数量" AS データ, T_支払.数量 AS 値
FROM T_支払
UNION ALL
SELECT T_支払.年月, T_支払.支払先,"金額", T_支払.金額
FROM T_支払
UNION ALL
SELECT T_支払.年月, "合計", "数量", T_支払.数量
FROM T_支払
UNION ALL
SELECT T_支払.年月, "合計", "金額", T_支払.金額
FROM T_支払) T
GROUP BY 支払先, データ
ORDER BY 支払先, データ DESC
PIVOT 年月;

お礼

ありがとうございました。

回答No.1

hatena1989

下記のようなテーブルを作成しておくと、クロス集計クエリ一つで可能です。

T_種別
------
ID 種別
1　数量
2　金額

テーブル名を T_支払 とすると下記のSQL文で希望の結果になります。

TRANSFORM Sum(Choose([T_種別].[ID],[数量],[金額])) AS 式1
SELECT IIf([T_種別_1].[ID]=2,"合計",[T_支払].[支払先]) AS 支払先, T_種別.種別
FROM T_支払, T_種別, T_種別 AS T_種別_1
GROUP BY IIf([T_種別_1].[ID]=2,"合計",[T_支払].[支払先]), T_種別.種別
ORDER BY IIf([T_種別_1].[ID]=2,"合計",[T_支払].[支払先]), T_種別.種別 DESC
PIVOT T_支払.年月;

お礼

ありがとうございます。
希望の結果になりましたが、
出来ることでしたら別テーブルは持ちたくないです。
アクセスのクロス集計を使わなくてもいいです
直接SQL文の入力で結構ですので別の方法はないでしょうか？
もしございましたらお願い致します。

わがまま言いますがよろしくお願いします