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
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