Convert columns to rows in oracle

Question

this is my table in oracle 11g:

**date          qty1            qty2             qty3           qty4**
2-Feb-14            61           64             52           54
2-Mar-14           124          130            149          156

i want to convert it into the following table. i.e. add 7 days to the date and transpose the qty. And i have till qty52 such metrics

***date        qty***
**2-Feb-14        61**
9-Feb-14          64
16-Feb-14         52
23-Feb-14         54
**2-Mar-14        124**
9-Mar-14          130
16-Mar-14         149
23-Mar-14         156

ajmalmhd04 · Answer 1 · 2014-01-09T06:50:38.187

have a try:

WITH t(my_date, val, val2, val3, val4)
AS ( 
SELECT to_date('01/01/2014 12:00:00 AM', 'dd/mm/yyyy hh:mi:ss am'), 1,2,3,4 from dual
UNION ALL
SELECT to_date('01/02/2014 12:00:00 AM', 'dd/mm/yyyy hh:mi:ss am'), 5,6,7,8 FROM dual
)
SELECT (my_date-7) + (row_number() OVER (partition by my_date ORDER BY my_date)*7)  my_date, value as qty
FROM (
     ( SELECT my_date, val, val2, val3, val4 FROM t
     ) unpivot ( value FOR value_type IN (val, val2, val3, val4) ) );

output:

MY_DATE                        QTY
----------------------- ----------
01/01/2014 12:00:00 AM           1 
08/01/2014 12:00:00 AM           2 
15/01/2014 12:00:00 AM           3 
22/01/2014 12:00:00 AM           4 
01/02/2014 12:00:00 AM           5 
08/02/2014 12:00:00 AM           6 
15/02/2014 12:00:00 AM           7 
22/02/2014 12:00:00 AM           8

score 1 · Accepted Answer · answered Jan 09 '14 at 06:29

1

select date,qty from
(select date,qty1 as qty
from tbl
union
select date+7 as date,qty2 as qty
from tbl
union
select date+14 as date,qty3 as qty
from tbl
union
select date+21 as date,qty4 as qty
from tbl)
order by date

answered Jan 09 '14 at 06:29

dpk

641
1
5
15

Thank you guys !! all the suggestions work !! – user3176209 Jan 13 '14 at 11:08

score 1 · Answer 3 · answered Jan 09 '14 at 06:44

If you've got Oracle 11g, I'd look at doing it with UNPIVOT.

select
  start_date + to_number(week_number) * 7,
qty
from (
  select *
  from quantity_data 
  unpivot (qty for week_number 
           in (qty1 as '0', qty2 as '1', qty3 as '2', qty4 as '3')) 
)

This is an alternative to the example from ajmalmhd04, using to_number instead of the row_number analytic function. The answer from ajmalmhd04 is probably more generic though

If you haven't got Oracle 11g then try this for an option:

with pivot_data as (
  select 0 as pivot_col from dual union all
  select 1 from dual union all
  select 2 from dual union all
  select 3 from dual
)
select 
  start_date + (7 * pivot_col) as start_date,
  case 
    when pivot_col = 0 then qty1
    when pivot_col = 1 then qty2
    when pivot_col = 2 then qty3
    when pivot_col = 3 then qty4 end as qty
from 
  quantity_data cross join pivot_data
order by 1

San · Answer 4 · 2014-01-09T06:42:46.877

Try this

with tab(date_d,qty1,qty2,qty3,qty4) as (
   select '2-Feb-14',61,64,52,54 from dual union all
   select '2-Mar-14',124,130,149,156 from dual),
tab2(dd, ss) as (select date_d, qty1||','||qty2||','||qty3||','||qty4  from tab)
select to_date(dd) + ((level-1) * 7) "DATE", regexp_substr(ss, '[^(,)]+', 1, level) "QTY"
  from tab2
connect by level <= length(ss) - length(replace(ss, ',')) + 1
  and prior ss = ss
  and prior sys_guid() is not null

output

|                            DATE | QTY |
|---------------------------------|-----|
|    March, 02 2014 00:00:00+0000 | 124 |
|    March, 09 2014 00:00:00+0000 | 130 |
|    March, 16 2014 00:00:00+0000 | 149 |
|    March, 23 2014 00:00:00+0000 | 156 |
| February, 02 2014 00:00:00+0000 |  61 |
| February, 09 2014 00:00:00+0000 |  64 |
| February, 16 2014 00:00:00+0000 |  52 |
| February, 23 2014 00:00:00+0000 |  54 |

Let me know if it meets your requirement.

Convert columns to rows in oracle

4 Answers4