pivot)ピボットテーブルbyエクセルじゃないよSQLだよ　※ピポットかな？

この記事を読むのに必要な時間は約 15 分です。

 

PIVOT

SQLで書くピボットテーブル

Excelで便利な機能であるピポットテーブルですが、SQLでも書くことができます。

出来るだけわかりやすく説明してみます。

ただ、SQLで書くピボットテーブルですが、相当

が発生しない限りは使わないと思います。

理由は、Excelでやった方が圧倒的に早いからです・・・・

 

テーブル作成

まずはテストデータを投入するためのテーブルを作成します。

以下のクエリでテスト用のテーブルを作成してください。

CREATE TABLE [dbo].[おさかな](
	[種別] [nvarchar](50) NULL,
	[月] [nvarchar](50) NULL,
	[名前] [nvarchar](50) NULL,
	[単価] [int] NULL,
	[数量] [int] NULL
) ON [PRIMARY]
GO

 

流し込むだけで作ってくれるはずです。

出来上がったら　上位1000行　を表示して確認してみましょう。

以下の通り、何も無い状態が確認できますね。

それでは次にテストデータを投入しますが、ここで書くとかなり長くなりますので、

このページの最下部にコピペ用のデータを準備しています。

お試しする人は下まで行ってコピーしてきてください。

 

挿入が完了したとして・・・・

 

投入結果を確認してみます。

改めて、　上位1000行　を表示して確認してみましょう。

以下の通りある程度データが入っている事が確認できます。

 

 

そもそも、ピボットテーブルで何ができるのか？

この基本が頭にないと、思うように書くことができないと思います。

行列の入れ替えだとかクロス集計だとかいろいろ言えますが、

と、思ってください。

 

 

 

うん。

自分で言っておきながら当たり前ですね。

これは例えが悪いですね・・・・・

 

と、思ってください。

こういえばしっくりきましたね。

 

そうです。

ピボットテーブルの出来上がりは集計です。

そのため、sum,count,avg等の集計関数を使うと言う事を頭に入れておいてください。

Excelのピボットテーブルではかなり　当たり前　の事ですが、　SQLのピボットテーブルもこの集計が基本となります。

 

まずは書いてみる。

単純にselect * from を書いて見ます。

この基本に対して、以下の赤点線で括った部分を付け足すような感じです。

この、

に、記載する内容ですが、以下のような構文となります。

＋＋＋＋＋

PIVOT
(
集計関数(集計したい対象) FOR 行にしたい元の列 IN (元の列の中にある項目たち)
) as 別名

＋＋＋＋＋

それぞれを説明してきます。

集計関数

イメージしやすいようにあえてExcelで表現するとこれ等です。

SQLで言えば、MAXとかMINとかAVGとかですね。

 

集計したい対象

今回のテストデータだとこれです。

 

他にも数量も可能ですね。

大事なのは、名前と月　ではないと言う事です。

以前に集計関数の説明をしたときと同じこと言いますが、

という不毛な質問はしないですよね。

集計するならば、単価などの数値データが基本となります。

※countで個数を数えるならばその限りではありませんが、その場合でも　単価　の行で十分です。

 

 

行にしたい元の列

ちょっと説明しにくいですが、今回のテストデータだとたまたま説明しやすいです。

今回のテストデータは

といったお題になると思います。もしくは

なので、月か名前になります。

※今回は月にしてみます。

元の列の中にある項目たち

と、いう事で　月　のデータに入っている、１月～12月　を列に持ってきます。

※下の図の選択している部分です。

これを構文に当てはめると

以下のようになります。

 

＋＋＋＋＋

PIVOT (
sum([単価]) FOR [月] IN ([ 1月], [ 2月], [ 3月], [ 4月], [ 5月], [ 6月], [ 7月], [ 8月], [ 9月], [ 10月], [ 11月], [ 12月])
) as P

＋＋＋＋＋

通してSQL文にするとこの通りです。

 

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
　　*
  FROM [sampleDB].[dbo].[おさかな]　 

  PIVOT (
  sum([単価]) FOR [月] IN ([ 1月], [ 2月], [ 3月], [ 4月], [ 5月], [ 6月], [ 7月], [ 8月], [ 9月], [ 10月], [ 11月], [ 12月])
) as P

 

SQL文を流してみます。

こんな感じです。

・

・・

・・・

・・・・・

わ、わかりにくいです。

やめましょう・・・・・

 

基本はシンプルで！！

Excelのピポットテーブルもですが、行に来る項目が複数あると集計結果がぱっと見わかりにくいです。

SQLも同じです。

もう一度以下の例を見てみます。

行に来る項目が、種別・名前・数量と３種類もあります。

テストデータの場合、幸い種別は魚という言葉の１種類しかないですが、名前と数量でデータが分散します。

PIVOTした結果が分かりにくくなります。※この時点で出力結果自体は正しく出ていますが、わかりにくいです。

慣れないうちは、行に来る値は１種類にしておきましょう。

では、テストデータは以下の用に加工して利用する事にしましょう。

SELECT  [月]
      ,[名前]
      ,sum([単価]*[数量])　as '売上'
  FROM [sampleDB].[dbo].[おさかな]
  group by 月,名前

 

この様な形なら、わかりやすいですね。

以前にもやった、Select結果を一気にテーブルに流し込むやり方でテーブルを再度、作ってみます。

おさかなテーブルからおさから２テーブルを作ります。

こんなかんじでーす。

select * into [おさかな2] from
(
SELECT  [月]
      ,[名前]
      ,sum([単価]*[数量])　as '売上'
  FROM [sampleDB].[dbo].[おさかな]
  group by 月,名前
  ) XXX

 

実行したら上位1000行を確認してみます。

ちゃんとデータが入っていますね。

それではこのテーブルを使って改めてピボットを実施してみます。

/****** SSMS の SelectTopNRows コマンドのスクリプト  ******/
SELECT 
　　*
  FROM [sampleDB].[dbo].[おさかな2]　 

  PIVOT (
  sum([売上]) FOR [月] IN ([ 1月], [ 2月], [ 3月], [ 4月], [ 5月], [ 6月], [ 7月], [ 8月], [ 9月], [ 10月], [ 11月], [ 12月])
) as P

実行結果についてだいぶ見やすくなりましたね。

 

ちょっとエクセルで検算してみましょう。

１月の鱈の8799が正しいか確認してみます。

エクセルで１月と鱈でフィルタした結果です。

ちゃんと、8799って出てますね。

この様な形でSQLを使ってピポットを表現する事が出来ました。

ただ列に持ってくる、例えば1月から12月は固定できます。

最悪、3月のデータが無くても3月と書いておいても違和感ないです。

ただ、月ではなく、名前を列側に持ってきてしまった場合で、途中から

太刀魚

が、データとして入ってきた場合はSQL文を書き換える必要があります。

ん～

SQL文で書けるとカッコいいですが、Excelでやった方が早い場合はさっさとExcelでやっちゃいましょう。

 

 

 

 

テストデータ

今回の実験で利用したテストデータです。

コピペで貼り付けれます。