Search code examples
oracle-databaseplsqloracle12coracle19c

Transpose multiple columns into rows


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!


Solution

  • 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