Transpose multiple columns into rows

Question

Oracle 12c/19c

I have inherited some data which is in a table in the below format (please don't beat me down for why would someone store the data in this format etc - I get it but have to work with what I have). Business wants the data with columns transposed into rows for months (Target format shown below).

I am able to get the data I want using the big union query listed below but wanted to see if anyone had any better/efficient way to write it. I am also thinking of creating a nightly job to transform this data into the new format (or create a mview).

Source:

ORDER_ID
ORDER_YEAR
QTY_JAN
QTY_FEB
QTY_MAR
QTY_APR
QTY_MAY
QTY_JUN
QTY_JUL
QTY_AUG
QTY_SEP
QTY_OCT
QTY_NOV
QTY_DEC

Target:

ORDER_ID
ORDER_YEAR
ORDER_MONTH
QTY

Query:

select order_id, order_year, '01' order_month, sum(qty_jan) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '02' order_month, sum(qty_feb) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '03' order_month, sum(qty_mar) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '04' order_month, sum(qty_apr) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '05' order_month, sum(qty_may) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '06' order_month, sum(qty_jun) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '07' order_month, sum(qty_jul) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '08' order_month, sum(qty_aug) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '09' order_month, sum(qty_sep) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '10' order_month, sum(qty_oct) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '11' order_month, sum(qty_nov) qty
from order_quantity
group by order_id, order_year
union
select order_id, order_year, '12' order_month, sum(qty_dec) qty
from order_quantity
group by order_id, order_year
;

Thanks in advance!

Answer 1

You can use the hierarchy query as follows:

SELECT ORDER_ID, ORDER_YEAR,
       DECODE(LVL,1,'January',2,'February', ...., 12,'December') AS ORDER_MONTH,
       DECODE(LVL,1,QTY_JAN,2,QTY_FEB, ...., 12,QTY_DEC) AS QTY
  FROM YOUR_TABLE
  CROSS JOIN (SELECT LEVEL AS LVL FROM DUAL CONNECT BY LEVEL <= 12);

Answer 2

The simplest way to do this is with the UNPIVOT operator, available since Oracle 11.1.

I included months with NULL quantity in the output (easy to modify if you don't need them). I also created two columns for each month - one for the month name and the other for the position of the month within the year. The name can be used in the SELECT clause (it will be shown in the output), while the "position" is only used in the ORDER BY clause.

I created sample data for testing in the WITH clause. Of course, that is not part of the real-life solution. Use your actual table and column names in the main query. I only included the first three months - it should be easy for you to change to include all 12 months.

with
  order_quantity (order_id, order_year, qty_jan, qty_feb, qty_mar) as (
    select 1001, 2018, 300, 450, 200 from dual union all
    select 1001, 2019, 400, 250, null from dual union all
    select 1001, 2020, 300, null, 200 from dual union all
    select 1002, 2019, 540, 230, null from dual union all
    select 1002, 2020, null, 300, 800 from dual
  )
select order_id, order_year, order_month, qty
from   order_quantity
unpivot include nulls (
    qty for (order_month, pos) in ( qty_jan as ('January' , 1)
                                  , qty_feb as ('February', 2)
                                  , qty_mar as ('March'   , 3)
                                  )
                      )
order by order_id, order_year, pos
;

Output:

  ORDER_ID ORDER_YEAR ORDER_MONTH        QTY
---------- ---------- ----------- ----------
      1001       2018 January            300
      1001       2018 February           450
      1001       2018 March              200
      1001       2019 January            400
      1001       2019 February           250
      1001       2019 March                 
      1001       2020 January            300
      1001       2020 February              
      1001       2020 March              200
      1002       2019 January            540
      1002       2019 February           230
      1002       2019 March                 
      1002       2020 January               
      1002       2020 February           300
      1002       2020 March              800