クロス集計

Ｅｘｃｅｌ　ＳＱＬでクロス集計がやりたいです。

支店番号 日付 売上
Ａ 2018/4/1 1000
Ｂ 2018/5/1 1001
Ｃ 2018/5/31 1002
Ａ 2018/6/30 1003
Ｂ 2018/7/30 1004
Ｃ 2018/8/29 1005
Ａ 2018/9/28 1006
Ｂ 2018/4/1 1007
Ｃ 2018/5/1 1008
Ａ 2018/5/31 1009
Ｂ 2018/6/30 1010
Ｃ 2018/7/30 1011
Ａ 2018/8/29 1012
Ｂ 2018/9/28 1013
Ｃ 2018/4/1 1014
Ａ 2018/5/1 1015
Ｂ 2018/5/31 1016
Ｃ 2018/6/30 1017
Ａ 2018/7/30 1018
Ｂ 2018/8/29 1019
Ｃ 2018/9/28 1020


実行結果

支店番号 4 5 6 7 8 9 　　ＴＯＴＡＬ
A 合計　　　　　　　　合計
B　　　　合計　　　　　　　　合計　　　　
C 合計　　　　　　　　合計

シートの名前はデータ
strSQL = ""
strSQL = strSQL & " TRANSFORM SUM(売上) AS 売上合計"
strSQL = strSQL & " SELECT 支店番号 , SUM(S.売上) AS 全支店合計"
strSQL = strSQL & " FROM "
strSQL = strSQL & " データ "
strSQL = strSQL & " GROUP BY データ.支店番号 "
strSQL = strSQL & " PIVOT データ.顧客番号; "

うまくいきませんでした。
わかる方お願い致します。

質問者からの補足コメント

• 支店番号　　 4月　 5月　 6月　 7月　 8月　 9月 　　ＴＯＴＡＬ
  A 合計　　　　　　　　
  B合計　　　　　　　　　　　
  C 合計　　
  ＳＱＬでやりたいのですが。

    補足日時：2018/08/22 11:13

• 

  このｺｰﾄﾞわたしにはまだ無理かもしれません。
  SQLでなんとか

  No.1の回答に寄せられた補足コメントです。 補足日時：2018/08/22 11:16

No.1 ベストアンサー 回答者： 30246kiku 回答日時： 2018/08/22 10:30

> strSQL = strSQL & " SELECT 支店番号 , SUM(S.売上) AS 全支店合計"

ここのところの S.売上 は、正しい指定ですか？
また、この指定では 合計 部分は２列目に表示されますが････
その辺は、目をつぶる？


> strSQL = strSQL & " PIVOT データ.顧客番号; "

ここの PIVOT は、項目名になる部分なので、
PIVOT Month(データ.日付)
とかにしないと、月を示す数字にはならないかと･･･



以下、参考程度で

提示あった表は Sheet1 の A1 ～
結果を Sheet2 の A1 ～

自力で行えば、合計部分をどこに･･･自由かと
速度的にも大差はないかと･･･


Option Explicit

Public Sub Samp1()
　　Dim dic As Object, dicM As Object, dicW As Object
　　Dim vA As Variant, vK As Variant, v As Variant, vW As Variant
　　Dim sF As String
　　Dim i As Long, j As Long, k As Long, m As Long, n As Long

　　Set dic = CreateObject("Scripting.Dictionary")
　　Set dicM = CreateObject("Scripting.Dictionary")

　　With Worksheets("Sheet1")
　　　　With .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
　　　　　　vA = .Resize(, 3).Value
　　　　End With
　　End With

　　For i = 2 To UBound(vA)
　　　　If (Not dic.Exists(vA(i, 1))) Then
　　　　　　dic.Add vA(i, 1), CreateObject("Scripting.Dictionary")
　　　　End If
　　　　Set dicW = dic(vA(i, 1))
　　　　m = Month(vA(i, 2))
　　　　dicW(m) = dicW(m) + vA(i, 3)
　　　　dicM(m) = Empty
　　Next

　　k = dic.Count + 2
　　ReDim vA(1 To k, 1 To dicM.Count + 2)
　　i = 1: j = 2
　　vA(i, 1) = "支店番号"
　　vA(k, 1) = "合計"
　　For Each v In dicM.Keys
　　　　vA(i, j) = v
　　　　dicM(v) = j
　　　　j = j + 1
　　Next
　　vA(i, j) = "合計"

　　For Each vK In dic.Keys
　　　　i = i + 1
　　　　vA(i, 1) = vK
　　　　Set dicW = dic(vK)
　　　　For Each v In dicW.Keys
　　　　　　n = dicM(v)
　　　　　　vW = dicW(v)
　　　　　　vA(i, n) = vW
　　　　　　vA(i, j) = vA(i, j) + vW
　　　　　　vA(k, n) = vA(k, n) + vW
　　　　　　vA(k, j) = vA(k, j) + vW
　　　　Next
　　Next

　　Application.ScreenUpdating = False
　　With Worksheets("Sheet2")
　　　　With .Range("A1").Resize(k, j)
　　　　　　.CurrentRegion.Clear
　　　　　　.Value = vA
　　　　　　If (j > 3) Then
　　　　　　　　With .Columns(2).Resize(, j - 2)
　　　　　　　　　　.Sort .Cells(1), xlAscending, , Header:=xlNo _
　　　　　　　　　　　　　　, Orientation:=xlSortRows
　　　　　　　　End With
　　　　　　End If
　　　　　　With .Resize(k - 1)
　　　　　　　　.Sort .Cells(1), xlAscending, , Header:=xlYes _
　　　　　　　　　　　　, Orientation:=xlSortColumns
　　　　　　End With
　　　　　　.Rows(1).HorizontalAlignment = xlCenter
　　　　　　.Borders.LineStyle = xlContinuous
　　　　End With
　　End With
　　Application.ScreenUpdating = True

　　Set dic = Nothing
　　Set dicM = Nothing
　　Set dicW = Nothing
End Sub

この回答へのお礼

有難うございます。

お礼日時：2018/08/22 13:01

No.2 回答者： 30246kiku 回答日時： 2018/08/22 11:32

＃１です

> このｺｰﾄﾞわたしにはまだ無理かもしれません。

使えと言っているわけではありませんが、やってみてはいないのですか？


> SQLでなんとか

修正点を何個かあげましたが、
曲がりなりにも動くものに変更はできなかったのですか？


※ 右端の TOTAL は、どこで実現したいのですか？
SQL 指定でですか？････　無理だと思います（方法を知らないだけかも･･･）
書き出した後に、付け加えるのですか？
２列目で出力して、列移動するのですか？


以上

この回答へのお礼

有難うございます。

お礼日時：2018/08/22 16:23