質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

Q&A

解決済

2回答

13871閲覧

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

AMK

総合スコア765

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

1グッド

3クリップ

投稿2016/05/28 01:05

編集2016/05/28 01:55

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

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

sql

1select 2 A.得意先コード 3,ms.得意先名1 4, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '04' THEN A.伝票合計売上金額 ELSE 0 END) AS "4月" 5, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '05' THEN A.伝票合計売上金額 ELSE 0 END) AS "5月" 6, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '06' THEN A.伝票合計売上金額 ELSE 0 END) AS "6月" 7, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '07' THEN A.伝票合計売上金額 ELSE 0 END) AS "7月" 8, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '08' THEN A.伝票合計売上金額 ELSE 0 END) AS "8月" 9, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '09' THEN A.伝票合計売上金額 ELSE 0 END) AS "9月" 10, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '10' THEN A.伝票合計売上金額 ELSE 0 END) AS "10月" 11, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '11' THEN A.伝票合計売上金額 ELSE 0 END) AS "11月" 12, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '12' THEN A.伝票合計売上金額 ELSE 0 END) AS "12月" 13, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '01' THEN A.伝票合計売上金額 ELSE 0 END) AS "1月" 14, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '02' THEN A.伝票合計売上金額 ELSE 0 END) AS "2月" 15, SUM(CASE SUBSTRING(A.売上見出日付,5,2) WHEN '03' THEN A.伝票合計売上金額 ELSE 0 END) AS "3月" 16 from[販売].[dbo].[売上見出トラン] A 17 18 inner join [販売].[dbo].[得意先マスタ]ms 19 on A.得意先コード =ms.得意先コード 20 inner join [販売].[dbo].[得意先分類1マスタ] B1 21 on ms.得意先分類1コード = B1.得意先分類1コード 22 inner join [販売].[dbo].[得意先分類2マスタ] B2 23 on ms.得意先分類2コード = B2.得意先分類2コード 24 25 JOIN [販売].[dbo].[年度] B ON SUBSTRING(A.売上見出日付,1,6) = B.年月 26 WHERE B.年度 = '2015' 27 and B1.得意先分類1コード is not null 28 and ms.得意先分類2コード is not null 29 and ms.得意先分類1コード = '01' 30 and ms.得意先分類2コード = '01' 31 GROUP BY A.得意先コード,ms.得意先名1 32 ORDER BY A.得意先コード 33

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

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

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

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

sql

1, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201501' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年1月" 2, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201601' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年1月" 3, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201502' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年2月" 4, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201602' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年2月" 5, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201503' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年3月" 6, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201603' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年3月" 7, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201504' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年4月" 8, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201604' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年4月" 9, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201505' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年5月" 10, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201605' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年5月" 11, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201506' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年6月" 12, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201606' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年6月" 13, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201507' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年7月" 14, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201607' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年7月" 15, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201508' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年8月" 16, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201608' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年8月" 17, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201509' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年9月" 18, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '201609' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年9月" 19, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2015010' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年10月" 20, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2016010' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年10月" 21, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2015011' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年11月" 22, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2016011' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年11月" 23, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2015012' THEN A.伝票合計売上金額 ELSE 0 END) AS "前年12月" 24, SUM(CASE SUBSTRING(A.売上見出日付,1,6) WHEN '2016012' THEN A.伝票合計売上金額 ELSE 0 END) AS "今年12月"
yodel👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

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

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

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

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

投稿2016/05/28 04:25

hirohiro

総合スコア2068

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

AMK

2016/05/28 06:12

, 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

2016/05/28 07:03 編集

テーブルがどうなっているか明示されていませんのでなんとも言えませんが 解りやすくするために 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

2016/05/28 08:34

お世話になります 回答ありがとうございます! サブクエリもあまりわかっていないなりに出来る限りやってみたのですが 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

2016/05/28 09:13 編集

そのサブクエリには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

2016/05/28 09:24

返信有り難うございます。 Group By抜いたら動きました!! しかし 下記のようになtableで hirohiro様の予想通り年の表示がなかったです。 01 02 03 04 05 06 07 08 09 10 11 12 得意先コード 数字 数字 数字 数字 ・・・・・・ 引き続き頑張って探してみます。
hirohiro

2016/05/28 10:45 編集

あら... 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

2016/05/30 03:24 編集

お世話になります hirohiro様 ありがとうございます! もう一度実行してみたらチャント思ったようになっていました。 (ただ、私がボケてただけかもしれませんwww) 下記のコードで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
guest

0

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

投稿2016/05/30 03:48

hirohiro

総合スコア2068

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

hirohiro

2016/05/30 03:50 編集

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

2016/05/30 04:47

> ISNULL(a, b) > aがNULL値でない場合はaを返し、aがNULL値の場合はbを返す。 こっちの方が一般的みたいですね。HITが多かったです。
AMK

2016/05/30 06:58

どちらでも行けました!! けど列名が、列名なしにwww
AMK

2016/05/30 07:01

あ、行けました!! 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

2016/05/30 07:03

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問