レベルエンター山本大のブログ

面白いプログラミング教育を若い人たちに

BLOCKVROCKリファレンス目次はこちら

SQLServer2005からの新機能「Pivot句」

SQLServer2005からの新機能「Pivot句」のおかげで、

昔なやんで、ゴリゴリ組んだ「テーブル行の横展開」が

一瞬にしてできるようになってる。


■やりたいこと

※Befor (このテーブルを)

USER_ID USER_NAME SEX
1 山本 1
2 山田 1
3 山下 0
4 山根 1
5 山木 0
6 山川 1

※After (こうしてみる)

title female male
男女比 2 4


性別ごとの列にして、それぞれの性別ごとにデータをカウントする

やりかた

■テーブルの準備

CREATE TABLE [dbo].[T_USER](
	[USER_ID] [int] NULL,
	[USER_NAME] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
	[SEX] [bit] NULL
)
insert into [dbo].[T_USER] ([USER_ID],[USER_NAME],[SEX])
select 1,'山本',1
union select 2,'山田',1
union select 3,'山下',0
union select 4,'山根',1
union select 5,'山木',0
union select 6,'山川',1


■Pivot句を含むクエリ

select 
	'男女比' as title ,
	[0] as female ,
	[1] as male
from 
	-- 展開するために必要な列だけに絞り込んだサブクエリとする
	(select USER_ID,SEX from dbo.[T_USER]) as u
PIVOT
(
	count(USER_ID)
	FOR u.SEX IN ([0],[1])
) AS PivotTable

■実行結果

title female male
男女比 2 4

基準列は、From句のすべての列

基準にする列はFrom句で選択されたで、
かつPivot句のForに含まれない列ということみたいなので、
上記のクエリを修正してFrom句にて「USER_NAME」を返すようにしたら、
結果は下記のようになる。

※ /*ここ⇒*/を修正

■Pivot句を含むクエリ

select 
	/*ここ⇒*/ USER_NAME ,
	[0] as female ,
	[1] as male
from 
	(select user_id,sex /*ここ⇒*/ ,USER_NAME from dbo.[T_USER]) as u
PIVOT
(
	count(user_id)
	FOR u.sex IN ([0],[1])
) AS PivotTable

■実行結果

USER_NAME female male
山下 1 0
山根 0 1
山川 0 1
山田 0 1
山本 0 1
山木 1 0