Crosstab with static column

Question

How can I make a static column/row in crosstab? See example below; can I have a fixed jan, feb, march, ... columns instead of it generating dynamically?

location       jan   feb   mar   apr   may   jun   jul   aug   sep   oct   nov   dec 
london         500   62    200   50    0     60    100   46    89    200   150   210
paris          50    26    20    500   50    70    40    200   0     40    250   50

I want the column (jan, feb, mar, apr, ...) to always show up regardless of their measures zero or have values. Like they are fixed.

Here is the query I'm using:

select sum("AMOUNT"), "REQUESTDATE","description" 
from(
SELECT SUM(e.AMOUNT)"AMOUNT",TO_CHAR(REQUESTDATE,'MM')"REQUESTDATE", CA.DESCR "description"
FROM PC_PAYMENTTRXNLOG PC,GLB_TYPE ca, PC_ESERVICEINQUIRY e
where PC.ESERVICE_ID = E.ID 
AND trunc(REQUESTDATE) between trunc(to_date('2012-01-01','yyyy-mm-dd')) and trunc(to_date('2012-06-30','yyyy-mm-dd'))
GROUP BY TO_CHAR(REQUESTDATE,'MM'),CA.DESCR 
 )
group by "REQUESTDATE","description"

and the output

    SUM("amount") Requestdate    Description
    2550405         04           A
    2550405         04           B
    23893281        05           C
    614977          06           A
    614977          06           E
    2550405         04           C

now after updated the query to be

  select sum("AMOUNT"), month,"description" 
  from(
  SELECT SUM(e.AMOUNT)"AMOUNT",TO_CHAR(REQUESTDATE,'MM')"REQUESTDATE", CA.DESCR "description"
  FROM PC_PAYMENTTRXNLOG PC,GLB_TYPE ca, PC_ESERVICEINQUIRY e
  where PC.ESERVICE_ID = E.ID 
  AND trunc(REQUESTDATE) between trunc(to_date('2012-01-01','yyyy-mm-dd')) and trunc(to_date('2012-06-30','yyyy-mm-dd'))
  GROUP BY TO_CHAR(REQUESTDATE,'MM'),CA.DESCR 
   ) 
 full outer join (select to_char(date '1970-01-01'
+ numtoyminterval(level - 1, 'month'), 'mm') as month
  from dual
  connect by level <= 12) on month="REQUESTDATE"
  group by month,"description"

when run the query run it displaying all the months regardless of their measures zero or have values.

BUT now the output is like that

location       jan   feb   mar   apr   may   jun   jul   aug   sep   oct   nov   dec 
london         500   62    200   50    0     60    100   46    89    200   150   210
paris          50    26    20    500   50    70    40    200   0     40    250   50
null            0     0     0     0     0     0     0     0    0      0     0     0

how i can restrict/hide the last null row?

Answer 1

have not tested it.. but try something like this

select sum("AMOUNT"), month,"description" 
from(SELECT SUM(e.AMOUNT)"AMOUNT",TO_CHAR(REQUESTDATE,'MM')"REQUESTDATE", CA.DESCR "description"
FROM PC_PAYMENTTRXNLOG PC,GLB_TYPE ca, PC_ESERVICEINQUIRY e
where PC.ESERVICE_ID = E.ID 
AND trunc(REQUESTDATE) between trunc(to_date('2012-01-01','yyyy-mm-dd')) and trunc(to_date('2012-06-30','yyyy-mm-dd'))
GROUP BY TO_CHAR(REQUESTDATE,'MM'),CA.DESCR 
 ) 
full outer join (select to_char(date '1970-01-01'
+ numtoyminterval(level - 1, 'month'), 'mm') as month
from dual
connect by level <= 12) on month="REQUESTDATE"
group by month,"description"

click here for SQL Fiddle demo to generate 1 to 12 in Oracle

Once you have generated this.. full outer join Your main query with this series query and take month from series query as I did in main query.

Using this query you will get all the data for all months with null values in measure.

For Description column - iReport set property's isRemoveLineWhenBlank and isBlankWhenNull to True, This will remove the null value being printed in iReport

For Measure use Print when expression in such a way, when ever description is null then return false. So this will prevent the value 0 being printed in iReport.