I would like to know if I can write an sql query that can generate multi-dimensional result set as shown below.
I am sure this can be done using OLAP, but i have no practical knowledge on OLAP.
I wrote my query as follows:
select sfrstcr_term_code term,
stvrsts_desc reg_status,
count(distinct SFRSTCR_PIDM) count
from sfrstcr,stvrsts
where sfrstcr_term_code = 201401
and SFRSTCR_ADD_DATE > to_date('21/02/2014','DD/MM/YYYY')
and SFRSTCR_RSTS_CODE is not null
and sfrstcr_camp_code = 'L'
and stvrsts_code = SFRSTCR_RSTS_CODE
group by sfrstcr_term_code, stvrsts_desc
union
select sfrstcr_term_code term,
stvrsts_desc reg_status,
count(distinct SFRSTCR_PIDM) count
from sfrstcr,stvrsts
where sfrstcr_term_code = 201301
and SFRSTCR_ADD_DATE > to_date('22/02/2013','DD/MM/YYYY')
and SFRSTCR_RSTS_CODE is not null
and sfrstcr_camp_code = 'L'
and stvrsts_code = SFRSTCR_RSTS_CODE
group by sfrstcr_term_code, stvrsts_desc
union
select sfrstcr_term_code term,
stvrsts_desc reg_status,
count(distinct SFRSTCR_PIDM) count
from sfrstcr,stvrsts
where sfrstcr_term_code = 201201
and SFRSTCR_ADD_DATE > to_date('24/02/2012','DD/MM/YYYY')
and SFRSTCR_RSTS_CODE is not null
and sfrstcr_camp_code = 'L'
and stvrsts_code = SFRSTCR_RSTS_CODE
group by sfrstcr_term_code, stvrsts_desc
order by 2,1
The result for the above query is as:
Thanks