SQL Server2005より増えたPIVOT君で遊んで見ました。
最終的には気に食わないことがあり、SQLステートメントブロックを組む形になりましたが・・・。
#今回も文章がなが～くなります。
#すみません。
では、お話をば。
まずは、PIVOTがどんなものなのかを、書いて見たいと思います。
サンプルのテーブルとして、商品名(item)、年度(year)、数量(val)を持つTestPivotテーブルを作りました。
入っているデータは以下の状態です。
| item |
year |
val |
| りんご |
2001 |
100 |
| りんご |
2002 |
200 |
| りんご |
2003 |
300 |
| りんご |
2004 |
400 |
| みかん |
2001 |
400 |
| みかん |
2002 |
300 |
| みかん |
2003 |
200 |
| みかん |
2004 |
100 |
| いちご |
2001 |
500 |
| いちご |
2002 |
500 |
| いちご |
2003 |
500 |
| いちご |
2004 |
500 |
| りんご |
2005 |
600 |
| りんご |
2005 |
600 |
このテーブルで、itemごとに年度が2001、2002、2003、2004年度の数量をを列にして出せたら見やすいですね。
表を先に出すと、こういう感じになります。
| item |
y2001 |
y2002 |
y2003 |
y2004 |
| いちご |
500 |
500 |
500 |
500 |
| みかん |
400 |
300 |
200 |
100 |
| りんご |
100 |
200 |
300 |
400 |
これをSQLで記述すると・・・・
======================================
SELECT
item,
SUM(CASE WHEN year = 2001 THEN val ELSE 0 END) AS y2001,
SUM(CASE WHEN year = 2002 THEN val ELSE 0 END) AS y2002,
SUM(CASE WHEN year = 2003 THEN val ELSE 0 END) AS y2003,
SUM(CASE WHEN year = 2004 THEN val ELSE 0 END) AS y2004
FROM TestPivot
GROUP BY item ;
======================================
となりますね。
ってこれはSQL Server 2000とか、他のDBMSではの書き方ですね。
SQL Server 2005で記述するとこうなります。
======================================
SELECT item, [2001] AS y2001, [2002] AS y2002, [2003] AS y2003, [2004] AS y2004
FROM
(
SELECT [item],[year],[val] FROM TestPivot
) T
PIVOT
(
SUM([val]) FOR [year] IN ( [2001],[2002],[2003],[2004] )
) AS PIVOT_TABLE;
======================================
だぁいぶ書き方が違いますね！
ポイントとしては
======================================
(
SELECT [item],[year],[val] FROM TestPivot
) T
======================================
で取得したいデータを書きます。
======================================
(
SUM([val]) FOR [year] IN ( [2001],[2002],[2003],[2004] )
) AS PIVOT_TABLE;
======================================
ここでyearが2001、2002、2003、2004の値を持つものについて、その値ごとにsum(value)しなさいって指定しています。
======================================
SELECT item, [2001] AS y2001, [2002] AS y2002, [2003] AS y2003, [2004] AS y2004
======================================
そうして、SELECT句で2001,2002,2003,2004の値ごとに集計した結果を出してくださいって指定しています。
#AS句で列に別名をつけています。
ここまでがPIVOTのざっくりとした説明ですが・・・・。
PIVOTのSQLに出てきた2001とかって固定値じゃないですかぁ・・・・。
今はデータが2004年までの4つしかないから良いけど、
これ、動的に変えたいですよね・・・・年度って増えるもんだし・・・・でも出来ないんですよね・・・。
で、おいらは最終的にSQLステートメントで動的SQLにしちゃいました♪
とりあえず、年度の上位4件(最新年度より4年分)をターゲットにPIVOT化♪
==========================================================
begin
/* 動的SQLを入れる変数 */
declare
@sqlstr varchar(max),
@sqldatas varchar(max),
@targetYear int;
/* yearをdesc(降順)で重複省き 上位4件をTOPで指定 */
declare cur cursor for
SELECT DISTINCT TOP 4 year FROM TestPivot ORDER BY year desc ;
set @sqldatas = '';
set @sqlstr = 'SELECT item';
open cur ;
fetch next from cur into @targetYear ;
while (@@fetch_status <> -1 )
begin
/* */
if len(@sqldatas) > 0
begin
set @sqldatas = @sqldatas + ','
end ;
/* [ value ] を作成*/
set @sqldatas = @sqldatas + '[' + cast(@targetYear as varchar) + ']'
/* [ value ] as y[value] を作成*/
set @sqlstr = @sqlstr + ',[' + cast(@targetYear as varchar) + '] as y' + cast(@targetYear as varchar) ;
fetch next from cur into @targetYear ;
end ;
close cur ;
deallocate cur ;
set @sqlstr = @sqlstr + ' FROM (SELECT [item],[year],[val] FROM TestPivot) T PIVOT (SUM([val]) FOR [year] IN ';
set @sqlstr = @sqlstr + '(' + @sqldatas + ')) AS PIVOT_TABLE' ;
/* デバッグ用にSQL出力した形跡♪ select @sqlstr ; */
execute (@sqlstr) ;
end ;
==========================================================
これを作って、データに、2005年度のりんご、数量＝600を2件追加。
実行してみました♪
| item |
y2005 |
y2004 |
y2003 |
y2002 |
| いちご |
NULL |
500 |
500 |
500 |
| みかん |
NULL |
100 |
200 |
300 |
| りんご |
1200 |
400 |
300 |
200 |
y2005の列が増えたし、りんごがちゃんと1200で出た♪
今度は2006年度のみかんで数量＝500を1件追加、再び実行。
| item |
y2006 |
y2005 |
y2004 |
y2003 |
| いちご |
NULL |
NULL |
500 |
500 |
| みかん |
500 |
NULL |
100 |
200 |
| りんご |
NULL |
1200 |
400 |
300 |
うん。大丈夫そうっす。
PIVOTの値指定が固定なので、どうしても使い勝手悪くなりそうですが、
上記のステートメントブロックなどを参考にし、
有効にPIVOTを使ってデータ分析するお手伝いが出来ればいいなぁってぐらいで
意味わからん今日のBLOGはおしまい(ぉぃ
#今日も元気にまとまりねぇ～
#すみませんです。