[Solved] How to generate view from Monthly data in rows converting it to columns in Oracle tables

by


The basic pattern you want is to have a case expression for each month, and only show the amount if the monthmatches:

select account, day, center, entity, year, frequency,
  case when month="Jan" then amount end as jan,
  case when month="Feb" then amount end as feb,
  case when month="Mar" then amount end as mar,
-- ... all the other months
  case when month="Dec" then amount end as dec
from source;

ACCOUNT       DAY CENTE ENTITY  YEAR FREQ        JAN        FEB        MAR ...        DEC
------------- --- ----- ------- ---- ---- ---------- ---------- ---------- ... ----------
A010100001000 D02 CC124 BBC0181 FY15 PATD -3185791.1
A010100001000 D02 CC13L BBC0181 FY15 PATD            -156955.28
A010100001000 D02 CC11B BBC0181 FY15 PATD                       -666443.03
...
A010100001000 D02 CCW4A BBC0181 FY15 PATD                                  ... -201339.86

If you could have more than one entry in a row in your output then it’s closer to a normal pivot, but you can still do the same thing with the addition of an aggregate and matching grouping:

select account, day, center, entity, year, frequency,
  max(case when month="Jan" then amount end) as jan,
  max(case when month="Feb" then amount end) as feb,
  max(case when month="Mar" then amount end) as mar,
-- ...
  max(case when month="Dec" then amount end) as dec
from source
group by account, day, center, entity, year, frequency;

3

solved How to generate view from Monthly data in rows converting it to columns in Oracle tables