Oracle: transpose data table (rows into columns)


create table MAIN_TBL (
magazine varchar2(10),
region varchar2(5),
quantity int );

insert into MAIN_TBL values ( 'Playboy', 'Nord', 1 );
insert into MAIN_TBL values ( 'Playboy', 'East', 2 );
insert into MAIN_TBL values ( 'AutoWeek', 'Nord', 3 );
insert into MAIN_TBL values ( 'AutoWeek', 'West', 4 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 5 );
insert into MAIN_TBL values ( 'Wired', 'Nord', 6 );
insert into MAIN_TBL values ( 'Wired', 'West', 7 );
insert into MAIN_TBL values ( 'Wired', 'East', 8 );
insert into MAIN_TBL values ( 'Wired', 'South', 9 );

declare
report_exists number;
report_owner varchar(30) := 'FRODO' ;
--
report_name varchar(30) := 'REPORT_TBL' ;
query_main varchar(16000) :=
'create table ' || report_owner || '.'
|| report_name || ' as select MAGAZINE ' ;
--
query_part varchar(1024) ;
my_var varchar2(5);

cursor cur_region is
select distinct REGION
from MAIN_TBL
order by region;

begin

select count(*) into report_exists
from dba_tables
where table_name = report_name and owner = report_owner;

if ( report_exists = 1 ) then
execute immediate 'drop table ' || report_owner || '.' || report_name ;
end if;

open cur_region ;
loop
fetch cur_region into my_var ;
exit when cur_region%NOTFOUND;
query_part := 'select nvl(sum(quantity),0) from MAIN_TBL x where x.magazine = main.magazine and x.region='''||my_var||'''' ;
query_main := query_main || chr(10) || ',(' || query_part || ')"' || my_var || '"';
end loop;

close cur_region ;

query_main := query_main || ' from (select distinct MAGAZINE from MAIN_TBL ) main' ;

execute immediate query_main ;

end;
/
select * from FRODO.REPORT_TBL
/

Output:

MAGAZINE East Nord South West
———- —– —– —— ——-
AutoWeek 0 3 0 4
Playboy 2 1 0 0
Wired 8 11 9 7

There is also discussion of the similar subject on AskTom site.

[amazon-product]0137142838[/amazon-product]