with tbl as (
     select '경기도 수원' addr, '남' sex, '10대' age from dual union all
     select '경기도 수원' addr, '여' sex, '10대' age from dual union all
     select '경기도 화성' addr, '여' sex, '30대' age from dual union all
     select '경기도 성남' addr, '남' sex, '50대' age from dual
)   
select addr
     , count(case when age = '10대' and sex = '남' then 1 end) male_10
     , count(case when age = '20대' and sex = '남' then 1 end) male_20
     , count(case when age = '30대' and sex = '남' then 1 end) male_30
     , count(case when age = '40대' and sex = '남' then 1 end) male_40
     , count(case when age = '50대' and sex = '남' then 1 end) male_50
     , count(case when age = '60대' and sex = '남' then 1 end) male_60
     , count(case when age = '10대' and sex = '여' then 1 end) female_10
     , count(case when age = '20대' and sex = '여' then 1 end) female_20
     , count(case when age = '30대' and sex = '여' then 1 end) female_30
     , count(case when age = '40대' and sex = '여' then 1 end) female_40
     , count(case when age = '50대' and sex = '여' then 1 end) female_50
     , count(case when age = '60대' and sex = '여' then 1 end) female_60
  from tbl
 group by addr
 order by addr
  

with t0 as (
select '경기도 수원' addr , '남' sex, '10대' ages from dual
union all
select '경기도 수원' addr , '여' sex, '10대' ages from dual
union all
select '경기도 화성' addr , '여' sex, '30대' ages from dual
union all
select '경기도 성남' addr , '남' sex, '50대' ages from dual
)
select addr
, sum(case when (sex = '남' and ages = '10대') then 1 else 0 end) "10대남자"
, sum(case when (sex = '남' and ages = '20대') then 1 else 0 end) "20대남자"
, sum(case when (sex = '남' and ages = '30대') then 1 else 0 end) "30대남자"
, sum(case when (sex = '남' and ages = '40대') then 1 else 0 end) "40대남자"
, sum(case when (sex = '남' and ages = '50대') then 1 else 0 end) "50대남자"
, sum(case when (sex = '남' and to_number(replace(ages, '대', '')) >= 60) then 1 else 0 end) "60이상 남자"
, sum(case when (sex = '여' and ages = '10대') then 1 else 0 end) "10대여자"
, sum(case when (sex = '여' and ages = '20대') then 1 else 0 end) "20대여자"
, sum(case when (sex = '여' and ages = '30대') then 1 else 0 end) "30대여자"
, sum(case when (sex = '여' and ages = '40대') then 1 else 0 end) "40대여자"
, sum(case when (sex = '여' and ages = '50대') then 1 else 0 end) "50대여자"
, sum(case when (sex = '여' and to_number(replace(ages, '대', '')) >= 60) then 1 else 0 end) "60이상 여자"
from t0
group by addr
;


with t0 as (
select '경기도 수원' addr , '남' sex, '10대' ages from dual
union all
select '경기도 수원' addr , '여' sex, '10대' ages from dual
union all
select '경기도 화성' addr , '여' sex, '30대' ages from dual
union all
select '경기도 성남' addr , '남' sex, '50대' ages from dual
)
select * from (
select addr, ages||sex as_col
from t0)
pivot (count(1) for as_col in ('10대남', '20대남', '30대남', '40대남', '50대남', '60대남'
                              ,'10대여', '20대여', '30대여', '40대여', '50대여', '60대여'))