PIVOT/UNPIVOT 句の紹介
PIVOT/UNPIVOT は SQL Server 2005 から追加されました。
FROM (Transact-SQL)
PIVOT と UNPIVOT の使用
PIVOT が 行を列に変換、UNPIVOT が 列を行に変換します。クロス集計のサンプルで紹介されたりします。
さっそく PIVOT から見ていきましょう。
構文はこんな感じです。
select ~ from <対象テーブル> pivot ( 集計関数( <集計対象列> ) for <グループ化対象列> in ( <グループ化対象列の値>, ... ) )
当てはめてみると…
[取引先], [担当者], [金額] 3列からなる [売上] テーブルから、取引先毎の担当者の売上件数を集計
select [取引先] , [田中 さん] , [鈴木 さん] , [伊藤 さん] , [佐藤 さん] from [売上] pivot ( count([金額]) for [担当者] in ( [田中 さん], [鈴木 さん], [伊藤 さん], [佐藤 さん]) ) as pvt
<グループ化対象列>の中から、行の値を in ( 〜 ) で並べていきます。指定した値が結果テーブルの列名になります。<グループ化対象列>は、nvarchar 型に明示的/暗黙的 に変換出来る必要があります。
また、<集計関数>、<グループ化対象列>は、1つしか指定出来ない模様。<対象テーブル> は当然サブクエリも使えます。
ラーメン屋に行ったデータを集計するため、テストデータの準備を行います。
use [テスト] create table [行ったラーメン屋] ( [主キー] int not null identity(1, 1) primary key, [日付] datetime not null, [ブランド] nvarchar(20) not null, [店舗] nvarchar(20) not null, [使った金額] int not null ) insert into [行ったラーメン屋] values ( '2011/10/01', '001', '北新地', 930) insert into [行ったラーメン屋] values ( '2011/10/05', '002', 'ルクア', 850) insert into [行ったラーメン屋] values ( '2011/10/12', '003', '元町', 750) insert into [行ったラーメン屋] values ( '2011/11/05', '001', '元町', 0) insert into [行ったラーメン屋] values ( '2011/12/25', '002', '道頓堀', 930) insert into [行ったラーメン屋] values ( '2011/12/25', '001', '北新地', 680) select * from [行ったラーメン屋]
まずは月を行、ブランドを列として月/ブランド毎の金額を集計してみます。
select [月] , [001] as [天上三品] , [002] as [神星座] , [003] as [さっくす] from ( select [ブランド], datepart(month, [日付]) as [月], sum([使った金額]) as [金額] from [行ったラーメン屋] group by [ブランド], datepart(month, [日付]) ) tmp pivot ( sum([金額]) for [ブランド] in ( [001], [002], [003] ) ) as pvt
ここでは店舗は不要のため、まずサブクエリで月/ブランド毎の金額を集計しています。
月の求め方は、DATEPART (Transact-SQL) を使って取得します。
次は逆のパターンで、ブランドを行、月を列にしてみましょう。
select [ブランド] , [10] as [10月] , [11] as [11月] , [12] as [12月] from ( select [ブランド], datepart(month, [日付]) as [月], sum([使った金額]) as [金額] from [行ったラーメン屋] group by [ブランド], datepart(month, [日付]) ) tmp pivot ( sum([金額]) for [月] in ( [10], [11], [12] ) ) as pvt
pivot for in で指定している列が変わっている位ですね。
月 は int 型でしたが、DateTime 型のカラムも列として指定することが出来ます。
ここでは、日付をそのまま列にしてみましょう。
select [ブランド] , [2011/10/12] , [2011/11/05] , [2011/12/25] from ( select [ブランド], [日付], sum([使った金額]) as [金額] from [行ったラーメン屋] group by [ブランド], [日付] ) tmp pivot ( sum([金額]) for [日付] in ( [2011/10/12], [2011/11/05], [2011/12/25] ) ) as pvt
日付のデータでも、10/12, 11/05, 12/25 の三日だけ出力するようにしています。
in ( 〜 ) で指定する列を増したら、結果セットの列数が変わります。ここでは 列名を "," 区切りで繰り返し記入が可能なだけで、式は使えません。
そのため出力する列数を変更する場合はクエリも変更する必要があります。*1
日付とブランド名で集計していましたが、当然他の観点でも集計出来ます。
店舗を行にし、ブランドを列にして行った回数を集計してみましょう。
select [店舗] , [001] as [天上三品] , [002] as [神星座] , [003] as [さっくす] from ( select [ブランド], [店舗], sum([使った金額]) as [金額] from [行ったラーメン屋] group by [ブランド], [店舗] ) tmp pivot ( count([金額]) for [ブランド] in ( [001], [002], [003] ) ) as pvt
sum が count に変わった位で、他とあまり変わりませんね。
PIVOT はこれ位にして、UNPIVOT を見ていきましょう。
構文はこちら
select ~ from <対象テーブル> unpivot ( <値が入る列名> for <複数の列を1列にまとめる列名> in ( <1つの列にまとめられる列名>, ... ) )
当てはめてみると…
[ブランド], [2011/10/12], [2011/11/05], [2011/12/25] の4列からなる [ラーメン屋に払った日付集計] テーブルから、ブランド、日付、金額 の3列に変換
select [ブランド] , [日付] , [金額] from [ラーメン屋に払った日付集計] unpivot ( [金額] for [日付] in ( [2011/10/12], [2011/11/05], [2011/12/25] ) ) as unpvt
1つの列にまとめた際の行の値を変更したい場合は、unpivot 前にサブクエリを利用して列名を変更します。
create table [取引先年度別集計] ( [取引先] nvarchar(20) not null primary key, [N-2年度] int not null default 0, [N-1年度] int not null default 0, [N年度] int not null default 0 ) insert into [取引先年度別集計] values ( 'A商社', 0, 16800, 50000 ) insert into [取引先年度別集計] values ( 'B商社', 15000, 22300, 35000 ) insert into [取引先年度別集計] values ( 'C商社', 32000, 34000, 30000 ) -- テストデータの確認 select * from [取引先年度別集計] -- UNPIVOT N=2012 として、行列変換 select [取引先], [年度], [金額] from ( select [取引先], [N-2年度] as [2010年度], [N-1年度] as [2011年度], [N年度] as [2012年度] from [取引先年度別集計] ) as targetdata unpivot ( [金額] for [年度] in ( [2010年度], [2011年度], [2012年度] ) ) as unpvt
UNPIVOT の例が少ないですが、こんな感じでどうでしょうか?
PIVOT/UNPIVOT を日本語で説明するの難しいので、クエリと結果を確認して伝わればなーと思います。
以下クエリ全文
use [テスト] -- PIVOT のテストデータ create table [行ったラーメン屋] ( [主キー] int not null identity(1, 1) primary key, [日付] datetime not null, [ブランド] nvarchar(20) not null, [店舗] nvarchar(20) not null, [使った金額] int not null ) insert into [行ったラーメン屋] values ( '2011/10/01', '001', '北新地', 930) insert into [行ったラーメン屋] values ( '2011/10/05', '002', 'ルクア', 850) insert into [行ったラーメン屋] values ( '2011/10/12', '003', '元町', 750) insert into [行ったラーメン屋] values ( '2011/11/05', '001', '元町', 0) insert into [行ったラーメン屋] values ( '2011/12/25', '002', '道頓堀', 930) insert into [行ったラーメン屋] values ( '2011/12/25', '001', '北新地', 680) select * from [行ったラーメン屋] -- PIVOT 月/ブランド毎の金額を集計 select [月] , [001] as [天上三品] , [002] as [神星座] , [003] as [さっくす] from ( select [ブランド], datepart(month, [日付]) as [月], sum([使った金額]) as [金額] from [行ったラーメン屋] group by [ブランド], datepart(month, [日付]) ) tmp pivot ( sum([金額]) for [ブランド] in ( [001], [002], [003] ) ) as pvt -- PIVOT ブランド/月毎の金額を集計 select [ブランド] , [10] as [10月] , [11] as [11月] , [12] as [12月] from ( select [ブランド], datepart(month, [日付]) as [月], sum([使った金額]) as [金額] from [行ったラーメン屋] group by [ブランド], datepart(month, [日付]) ) tmp pivot ( sum([金額]) for [月] in ( [10], [11], [12] ) ) as pvt -- PIVOT ブランド/日付毎の金額を集計 select [ブランド] , [2011/10/12] , [2011/11/05] , [2011/12/25] from ( select [ブランド], [日付], sum([使った金額]) as [金額] from [行ったラーメン屋] group by [ブランド], [日付] ) tmp pivot ( sum([金額]) for [日付] in ( [2011/10/12], [2011/11/05], [2011/12/25] ) ) as pvt -- PIVOT 店舗/ブランドの件数 select [店舗] , [001] as [天上三品] , [002] as [神星座] , [003] as [さっくす] from ( select [ブランド], [店舗], sum([使った金額]) as [金額] from [行ったラーメン屋] group by [ブランド], [店舗] ) tmp pivot ( count([金額]) for [ブランド] in ( [001], [002], [003] ) ) as pvt go drop table [行ったラーメン屋] go -- UNPIVOT のテストデータ create table [取引先年度別集計] ( [取引先] nvarchar(20) not null primary key, [N-2年度] int not null default 0, [N-1年度] int not null default 0, [N年度] int not null default 0 ) insert into [取引先年度別集計] values ( 'A商社', 0, 16800, 50000 ) insert into [取引先年度別集計] values ( 'B商社', 15000, 22300, 35000 ) insert into [取引先年度別集計] values ( 'C商社', 32000, 34000, 30000 ) -- テストデータの確認 select * from [取引先年度別集計] -- UNPIVOT N=2012 として、行列変換 select [取引先], [年度], [金額] from ( select [取引先], [N-2年度] as [2010年度], [N-1年度] as [2011年度], [N年度] as [2012年度] from [取引先年度別集計] ) as targetdata unpivot ( [金額] for [年度] in ( [2010年度], [2011年度], [2012年度] ) ) as unpvt go drop table [取引先年度別集計]
*1:同じクエリを使って動的に列数を変えることは出来ない