Hi,
from the small test data sample, I'm trying to transpose / pivot the columns valued Prov1, Prov2, Prov3 to rows and row values in the coverage column to column titles to look like this ... I've tried using decode but not working out for me ... any help would be appreciated.
prov comprehensive full open partial
prov1 13 33 98 81
prov2 25 15 59 78
prov3 60 77 48 62
drop table tmp_pvt_test;
create table tmp_pvt_test
(
coverage varchar2(25)
, prov1 number
, prov2 number
, prov3 number
);
insert into tmp_pvt_test
(coverage, prov1, prov2, prov3)
select 'comprehensive', 13, 25, 60 from dual
union
select 'full', 33, 15, 77 from dual
union
select 'partial', 81, 78, 62 from dual
union
select 'open', 98, 59, 48 from dual;
select * from tmp_pvt_test;
select
prov1, prov2
, max(decode(coverage, 'comprehensive', prov1,0)) comprehensive
, max(decode(coverage, 'full', prov1,0)) full
, max(decode(coverage, 'partial', prov1, 0)) partial
, max(decode(coverage, 'open', prov1, 0)) open
, max(decode(coverage, 'comprehensive', prov2,0)) comprehensive
, max(decode(coverage, 'full', prov2,0)) full1
, max(decode(coverage, 'partial', prov2, 0)) partial
, max(decode(coverage, 'open', prov2, 0)) open1
from tmp_pvt_test
group by prov1, prov2;