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
/
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]
Leave a Reply