sql study for pivot

test.sql

--use
--http://sqlfiddle.com/

--table
create table address(
  name varchar(100), pref varchar(100),age int
);

insert into address(name,pref,age) values('野口','埼玉',30);
insert into address(name,pref,age) values('深谷','東京',20);
insert into address(name,pref,age) values('村田','埼玉',31);
insert into address(name,pref,age) values('持田','長野',7);
insert into address(name,pref,age) values('本庄','群馬',45);
insert into address(name,pref,age) values('吉野','神奈川',56);
insert into address(name,pref,age) values('熊谷','群馬',85);
insert into address(name,pref,age) values('元町','神奈川',50);
insert into address(name,pref,age) values('園田','群馬',35);
insert into address(name,pref,age) values('川上','神奈川',26);
insert into address(name,pref,age) values('青木','群馬',15);
insert into address(name,pref,age) values('間宮','栃木',96);
insert into address(name,pref,age) values('畑沢','茨城',51);
insert into address(name,pref,age) values('武田','茨城',59);
insert into address(name,pref,age) values('大野','東京',28);
insert into address(name,pref,age) values('岸田','東京',88);
insert into address(name,pref,age) values('渡辺','東京',14);
insert into address(name,pref,age) values('青木','群馬',15);
insert into address(name,pref,age) values('間宮','栃木',96);
insert into address(name,pref,age) values('畑沢','茨城',81);
insert into address(name,pref,age) values('武田','茨城',89);
insert into address(name,pref,age) values('大野','東京',88);
insert into address(name,pref,age) values('岸田','東京',98);
insert into address(name,pref,age) values('渡辺','東京',94);
insert into address(name,pref,age) values('高崎','群馬',4);

--query
select 
  pref,
  sum(case when age < 10 then 1 else 0 end ) as '児童'
  ,sum(case when age between 10 and 19 then 1 else 0 end ) as '10代'
  ,sum(case when age between 20 and 29 then 1 else 0 end ) as '20代'
  ,sum(case when age between 30 and 39 then 1 else 0 end ) as '30代'
  ,sum(case when age between 40 and 49 then 1 else 0 end ) as '40代'
  ,sum(case when age between 50 and 59 then 1 else 0 end ) as '50代'
  ,sum(case when age between 60 and 69 then 1 else 0 end ) as '60代'
  ,sum(case when age > 69 then 1 else 0 end ) as '高齢者'

from address
group by pref
;

result

pref	児童	10代	20代	30代	40代	50代	60代	高齢者
埼玉	0	0	0	2	0	0	0	0
東京	0	1	2	0	0	0	0	4
栃木	0	0	0	0	0	0	0	2
神奈川	0	0	1	0	0	2	0	0
群馬	1	2	0	1	1	0	0	1
茨城	0	0	0	0	0	2	0	2
長野	1	0	0	0	0	0	0	0