SQLserver 年度・月別集計 前年度対比表

Question

いつもお世話になっています。

とりあえず解らないなりに下記のサイトを見て月別年度の横集計は出来ました。
http://www.dbsheetclient.jp/blog/?p=620

select
 A.得意先コード
,ms.得意先名1
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '04' THEN A.伝票合計売上金額 ELSE 0 END) AS "4月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '05' THEN A.伝票合計売上金額 ELSE 0 END) AS "5月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '06' THEN A.伝票合計売上金額 ELSE 0 END) AS "6月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '07' THEN A.伝票合計売上金額 ELSE 0 END) AS "7月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '08' THEN A.伝票合計売上金額 ELSE 0 END) AS "8月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '09' THEN A.伝票合計売上金額 ELSE 0 END) AS "9月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '10' THEN A.伝票合計売上金額 ELSE 0 END) AS "10月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '11' THEN A.伝票合計売上金額 ELSE 0 END) AS "11月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '12' THEN A.伝票合計売上金額 ELSE 0 END) AS "12月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '01' THEN A.伝票合計売上金額 ELSE 0 END) AS "1月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '02' THEN A.伝票合計売上金額 ELSE 0 END) AS "2月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '03' THEN A.伝票合計売上金額 ELSE 0 END) AS "3月"
 from[販売].[dbo].[売上見出トラン] A

             inner join [販売].[dbo].[得意先マスタ]ms
	  on A.得意先コード =ms.得意先コード
	  inner join [販売].[dbo].[得意先分類1マスタ] B1
	  on ms.得意先分類1コード = B1.得意先分類1コード
	  inner join [販売].[dbo].[得意先分類2マスタ] B2
	  on ms.得意先分類2コード = B2.得意先分類2コード

 JOIN [販売].[dbo].[年度] B ON SUBSTRING(A.売上見出日付,1,6) = B.年月
 WHERE B.年度 = '2015'
          and  B1.得意先分類1コード is not null
        and ms.得意先分類2コード is not null
        and ms.得意先分類1コード = '01'
        and ms.得意先分類2コード = '01'
 GROUP BY A.得意先コード,ms.得意先名1
 ORDER BY A.得意先コード

ここから更に発展で前年度の月別対比を集計するにはどのようにしてすればよいのでしょうか？
色々と検索してみましたが当方がわかる内容がヒットしなかったので質問させていただきます。
イメージとしては下記のような表組みをイメージしております。

     4月5月6月7月8月9月10月11月12月1月2月3月

得意先名 2014年度
2015年度
得意先名 2014年度
2015年度
得意先名 2014年度
2015年度

下記のようにすれば出せないことも無いのですが・・・
もっとスマートなやり方を知りたいです。

, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201501' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年1月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201601' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年1月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201502' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年2月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201602' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年2月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201503' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年3月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201603' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年3月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201504' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年4月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201604' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年4月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201505' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年5月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201605' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年5月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201506' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年6月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201606' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年6月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201507' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年7月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201607' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年7月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201508' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年8月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201608' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年8月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201509' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年9月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201609' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年9月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2015010' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年10月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2016010' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年10月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2015011' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年11月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2016011' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年11月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2015012' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年12月"
, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2016012' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年12月"

Answer 1

SQLServerはPivot句をサポートしているようですので、それを使うのが簡単かも知れません。
SQL ServerのPIVOT句・UNPIVOT句

動的SQLにすると、いくら種類が増えても修正する必要はなくなります。

上記のSQLを動的SQLに修正したものは下記のとおりになります。

参考ページの上記引用部分周囲のコードが参考になると思います。

Comments

[AMK]

, CASE SUBSTRING(A.売上見出日付,1,4) WHEN '2014' THEN A.伝票合計売上金額 ELSE 0 END AS "2014年"
, CASE SUBSTRING(A.売上見出日付,1,4) WHEN '2015' THEN A.伝票合計売上金額 ELSE 0 END AS "2015年"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '01' THEN A.伝票合計売上金額 ELSE 0 END) AS "1月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '02' THEN A.伝票合計売上金額 ELSE 0 END) AS "2月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '03' THEN A.伝票合計売上金額 ELSE 0 END) AS "3月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '04' THEN A.伝票合計売上金額 ELSE 0 END) AS "4月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '05' THEN A.伝票合計売上金額 ELSE 0 END) AS "5月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '06' THEN A.伝票合計売上金額 ELSE 0 END) AS "6月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '07' THEN A.伝票合計売上金額 ELSE 0 END) AS "7月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '08' THEN A.伝票合計売上金額 ELSE 0 END) AS "8月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '09' THEN A.伝票合計売上金額 ELSE 0 END) AS "9月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '10' THEN A.伝票合計売上金額 ELSE 0 END) AS "10月"
, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '11' THEN A.伝票合計売上金額 ELSE 0 END) AS "11月"

2015と2014をuppivotすればいいということでしょうか？？

[hirohiro]

テーブルがどうなっているか明示されていませんのでなんとも言えませんが
解りやすくするために
select 得意先コード
,得意先名
,SUBSTRING(A.売上見出日付,1,4) AS y
,SUBSTRING(A.売上見出日付,5,2) AS m
,伝票合計売上金額
FROM ....
こういった感じで年,月がカラムとして独立しているサブクエリを用意するとして、

SELECT [得意先コード],[得意先名],[y],[01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12]
FROM (上記サブクエリ) data
PIVOT (SUM([伝票合計売上金額]) FOR [m] IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) AS PV;
こんな風にするイメージです。

01 ~ 12 を直接書くのが冗長なら、回答で引用したページの「動的SQL」の部分を参照されればよろしいかと

またソートや2014年以上に限定したい場合は、上記を更にサブクエリにして
select *
from (2番目のクエリ) AS base
WHERE base.y >= 2014
ORDER BY base.得意先名,base.y

こんな感じ

[AMK]

お世話になります
回答ありがとうございます！

サブクエリもあまりわかっていないなりに出来る限りやってみたのですが

SELECT [得意先コード],[y],[01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12]
FROM (
select
ms.得意先コード
,SUBSTRING(売上見出日付,1,4) AS "y"
,SUBSTRING(売上見出日付,5,2) AS "m"
,伝票合計売上金額
from[販売].[dbo].[売上見出トラン]a
inner join [販売].[dbo].[得意先マスタ]ms
on a.得意先コード =ms.得意先コード
GROUP BY a.得意先コード

) data
PIVOT (SUM([伝票合計売上金額]) FOR [m] IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) AS PV;

こんな感じで
メッセージ 8120、レベル 16、状態 1、行 5
列 '販売.dbo.得意先マスタ.得意先コード' は選択リスト内では無効です。この列は集計関数または GROUP BY 句に含まれていません。

エラーが出てしまいます。

引き続き、頑張ってみますがヒントなど有りましたら大変助かります。

[hirohiro]

そのサブクエリにはGroup by は不要ですね。
単なるデータに、年と月のカラムを加えた状態のテーブルを生成するイメージです。

とすると何処にもGroup by句が無くなることに成りますが
実はSQLServerは使った事が無いので、動作試せないのですが、参考に貼ったサイトを見る限り、PIVOTの要素にすると勝手にGROUP化されているように見えます。
PIVOT (SUM([伝票合計売上金額]) FOR [m] IN ([01],....
これはmでグループ化して01.02別にsum集計した値を出していますが
selectで　[得意先コード],[y],[01]....
このように得意先コードとyを指定することで、コードとyでグループ化されたうえで、mでグループして集計するのではないでしょうか？
もし認識が違っていたら、外側のクエリ(サブクエリではない)でコードとyでGroupする必要はあるかも知れません。

サブクエリというのは括弧の中に記述されているSQLクエリのことです。
SQLが実行される際に括弧の深い階層のものから順に実行され、その結果は上位のクエリでテーブルのように扱われます。
新たに生成されたテーブル(?)なので、参照元テーブルのindexが使えない(から大量のデータを扱う場合遅い)などのデメリットがあります。

[AMK]

返信有り難うございます。

Group By抜いたら動きました！！

しかし　下記のようになtableで
hirohiro様の予想通り年の表示がなかったです。

01 02 03 04 05 06 07 08 09 10 11 12
得意先コード　数字　数字　数字　数字　・・・・・・

引き続き頑張って探してみます。

[hirohiro]

あら...　Group項目が複数だと駄目なのでしょうか。
・・・・
SELECT [code],[01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12]
FROM (
SELECT
ms.得意先コード || '_' || SUBSTRING(売上見出日付,1,4) AS code
,SUBSTRING(売上見出日付,5,2) AS "m"
,伝票合計売上金額
FROM [販売].[dbo].[売上見出トラン]a
inner join [販売].[dbo].[得意先マスタ]ms
on a.得意先コード =ms.得意先コード
GROUP BY a.得意先コード

) data
PIVOT (SUM([伝票合計売上金額]) FOR [m] IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) AS PV
ORDER BY code
・・・・
もしやこれだと動くのでしょうか...
(上記の結果でも良いなら不要ですが)もしそうなら、withを使った再帰集計のほうが良いかも知れませんね。

[AMK]

お世話になります
hirohiro様
ありがとうございます！
もう一度実行してみたらチャント思ったようになっていました。
（ただ、私がボケてただけかもしれませんｗｗｗ）

下記のコードでOKです。

欲を言えば、NULLが出ているのでそれを0の値にしてみたいです＾＾；

SELECT [得意先コード],得意先名1,[y],[01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12]
FROM (
select *
from (
select
ms.得意先コード,ms.得意先名1
,SUBSTRING(売上見出日付,1,4) AS "y"
,SUBSTRING(売上見出日付,5,2) AS "m"
,伝票合計売上金額
from[販売].[dbo].[売上見出トラン]a
inner join [販売].[dbo].[得意先マスタ]ms
on a.得意先コード =ms.得意先コード
where ms.得意先分類1コード is not null
and ms.得意先分類2コード is not null
and ms.得意先分類1コード = '01'
and ms.得意先分類2コード = '01'
) AS base
WHERE base.y >= 2014
) data

PIVOT (SUM([伝票合計売上金額]) FOR [m] IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) AS PV
order by 得意先コード,y asc

Answer 2

Pivotでも通用するかわかりませんが、Nullを0として出力したい場合は関数で「COALESCE(フィールド名,0)」こんな感じに処理する方法があります。
SELECT COALESCE([01],0)
こんな感じで動きませんかね？ちょっと自信ないですけど。

Comments

[hirohiro]

間違って新規投稿してしまいました。これって多分消せないんですよね...
「AsaoMako 2016/05/30 12:24 編集 」このコメントへの返信内容です。

[hirohiro]

> ISNULL(a, b)
> aがNULL値でない場合はaを返し、aがNULL値の場合はbを返す。

こっちの方が一般的みたいですね。HITが多かったです。

[AMK]

どちらでも行けました！！
けど列名が、列名なしにｗｗｗ

[AMK]

あ、行けました！！

SELECT [得意先コード]
,得意先名1
,[y]AS "年"
, COALESCE([01],0) AS "01月"
, COALESCE([02],0) AS "02月"
, COALESCE([03],0) AS "03月"
, COALESCE([04],0) AS "04月"
, COALESCE([05],0) AS "05月"
, COALESCE([06],0) AS "06月"
, COALESCE([07],0) AS "07月"
, COALESCE([08],0) AS "08月"
, COALESCE([09],0) AS "09月"
, COALESCE([10],0) AS "10月"
, COALESCE([11],0) AS "11月"
, COALESCE([12],0) AS "12月"
FROM (
select *
from (
select
ms.得意先コード,ms.得意先名1
,SUBSTRING(売上見出日付,1,4) AS "y"
,SUBSTRING(売上見出日付,5,2) AS "m"
,伝票合計売上金額
from[販売].[dbo].[売上見出トラン]a
inner join [販売].[dbo].[得意先マスタ]ms
on a.得意先コード =ms.得意先コード
where ms.得意先分類1コード is not null
and ms.得意先分類2コード is not null
and ms.得意先分類1コード = '01'
and ms.得意先分類2コード = '01'
) AS base
WHERE base.y >= 2014
) data

PIVOT (SUM([伝票合計売上金額]) FOR [m] IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])) AS PV

order by 得意先コード,y desc

[AMK]

何から何までありがとうございます！
100%理解したわけでは無いですが、おかげさまで理解度がUpした実感は有ります！！