SQLで列多段Pivotクロス集計を行う方法

以前にADODB-SQLでクロス集計を行う方法を色々ご紹介した。

www.excellovers.com

今回新たにクロス集計のネタに遭遇したので、調べた結果を残しておく。

やりたいこと

上図はピボットテーブルで作成したものです。 今回行いたいことは、ピボットテーブルの列項目を複数要素選択したものと同じ状態をSQLで再現することです。

SQLでクロス集計

元ネタはいつものごとくなんちゃって個人情報より。

元ネタから上図のクロス集計を実現するためのSQLは次のようになります。

transform count(*) select 都道府県 from [Sheet1$]
 group by 都道府県 pivot 性別

行に複数項目のPivotのためのSQL

まずは行の項目を複数に設定してみます。 下記のようなSQLで実現することが出来ます。

transform count(*) select 都道府県,血液型 from [Sheet1$]
  group by 都道府県,血液型 pivot 性別

SelectとGroup byに追加するだけでOKです。

列に複数項目は出来ない

では同じように列の項目を複数にしたSQLを作成・実行すると、下記のようなエラーが表示され実行できません。

transform count(*) select 都道府県 from [Sheet1$]
  group by 都道府県 pivot 性別,血液型

※コンマを正しく入力したら動くのか

ではどのようにすれば列の項目を複数にしたクロス集計を実現できるのでしょうか。

「＋」で項目結合

下記のように集計したい項目「+」で結合してやることで、列に複数項目を設定したクロス集計を実現することが出来ます。

transform count(*) select 都道府県 from [Sheet1$]
　　group by 都道府県 pivot 性別+血液型

ヘッダー部分が2行にはなっていませんが、正しく値は想定通りに集計されています。 ヘッダー部分をピボットテーブルと同じようにしたいのであれば、別途VBAで調整するようにしましょう。

コード全文

Sub クロス集計()
'--Microsoft ActiveX Data Objects X.X Libraryを参照設定
    Dim myCON As ADODB.Connection: Set myCON = New ADODB.Connection
    myCON.Provider = "Microsoft.ACE.OLEDB.12.0"
    myCON.Properties("Extended Properties") = "Excel 12.0;IMEX=1"
    myCON.Open ThisWorkbook.FullName
    
    Dim myRS As ADODB.Recordset: Set myRS = New ADODB.Recordset
    Dim mySQL As String
'    mySQL = "transform count(*) select 都道府県 from [Sheet1$] group by 都道府県 pivot 性別" 'クロス集計
'    mySQL = "transform count(*) select 都道府県,血液型 from [Sheet1$] group by 都道府県,血液型 pivot 性別" '行複数
    mySQL = "transform count(*) select 都道府県 from [Sheet1$] group by 都道府県 order by 都道府県 desc pivot 性別+血液型" '列複数
    myRS.Open mySQL, myCON, adOpenStatic
    
    ws出力.Cells.Clear
    Dim i As Long
    For i = 1 To myRS.Fields.Count
        ws出力.Cells(1, i).Value = myRS.Fields(i - 1).Name
    Next
    ws出力.Range("A2").CopyFromRecordset myRS
    
    myRS.Close: Set myRS = Nothing
    myCON.Close: Set myCON = Nothing
End Sub