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

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

ただいまの
回答率

89.12%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 3
  • VIEW 8,808

AMK

score 515

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

とりあえず解らないなりに下記のサイトを見て月別年度の横集計は出来ました。
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月"
  • 気になる質問をクリップする

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+2

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

動的SQLにすると、いくら種類が増えても修正する必要はなくなります。
上記のSQLを動的SQLに修正したものは下記のとおりになります。

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/05/28 18:24

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

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

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

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

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

    キャンセル

  • 2016/05/28 19:41 編集

    あら... 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を使った再帰集計のほうが良いかも知れませんね。

    キャンセル

  • 2016/05/30 11:33 編集

    お世話になります
    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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2016/05/30 15:58

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

    キャンセル

  • 2016/05/30 16: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

    キャンセル

  • 2016/05/30 16:03

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

    キャンセル

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

  • ただいまの回答率 89.12%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる