0

I would like to know if I can write an sql query that can generate multi-dimensional result set as shown below.

enter image description here

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:

enter image description here

Thanks

Avinesh Kumar
  • 1,389
  • 2
  • 17
  • 26
  • 1
    Your 3 union clause are identical except for the date portion...you can rewrite that to be one statement with a group by clause pretty readily. The second part...you are looking for a 'pivot'. Google (or search here on SO) for oracle pivot – Twelfth Aug 20 '14 at 22:27
  • Thanks @Twelfth Pivot works fine for me. – Avinesh Kumar Aug 21 '14 at 00:57

0 Answers0