Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Girish.

Asked: February 07, 2019 - 6:47 am UTC

Last updated: February 07, 2019 - 10:00 am UTC

Version: 18

Viewed 1000+ times

You Asked

Hi,

I have data as given below in table

create table chart(
SERIES  varchar2(10),
LABEL   date,
VALUE   number(5,2)
);

insert into chart values('A',to_date('13-DEC-18','DD-MON-YY'),83.55);
insert into chart values('B',to_date('13-DEC-18','DD-MON-YY'),7);
insert into chart values('C',to_date('13-DEC-18','DD-MON-YY'),7);

I need to convert this data as

         A             B            C
LABEL 13-DEC-18     13-DEC-18   13-DEC-18
VALUE 83.55            7           7

Need help in converting data

Thanks,
Girish

and Chris said...

So, you need to make:

- label & value rows (unpivot)
- Series columns (pivot)

You can do this by chaining together unpivot + pivot

with rws as ( 
  select series, 
         to_char ( label, 'DD-MON-YYYY' ) label,
         to_char ( value ) value
  from   chart 
)
  select * from rws
  unpivot (
    val for col in (
      label, value 
    )
  )
  pivot (
    max ( val ) for series in (
      'A' a, 'B' b, 'C' c
    ) 
  )
  order  by col;

COL     A             B             C             
LABEL   13-DEC-2018   13-DEC-2018   13-DEC-2018   
VALUE   83.55         7             7      


You can read more about how these work at: https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.