Document Type | Technical Information
CategoryㅣAdministration
Applicable Product Version | Tibero 6
Document Number | TADTI079
Overview
There are three methods to perform data PIVOT as follows.
- PIVOT using SUM(DECODE()) syntax
- Dynamic PIVOT using a procedure
- PIVOT syntax-based PIVOT
This document explains how to dynamically perform a PIVOT using a procedure. The dynamic PIVOT method is useful in environments where column values change frequently.
NoteThe test environment used Tibero 6 (DB 6.0 FS06_CS_1703) version.
Method
Creating Test Table
CREATE TABLE TEST( YEARDATA VARCHAR(4), DATA1 VARCHAR(6), DATA2 VARCHAR(6) );
Data Insertion
INSERT INTO TEST VALUES ('1990','100000','999999');
INSERT INTO TEST VALUES ('1991','100001','999998');
INSERT INTO TEST VALUES ('1992','100002','999997');
INSERT INTO TEST VALUES ('1993','100003','999996');
INSERT INTO TEST VALUES ('1994','100004','999995');
INSERT INTO TEST VALUES ('1995','100005','999994');
INSERT INTO TEST VALUES ('1996','100006','999993');
INSERT INTO TEST VALUES ('1997','100007','999992');
INSERT INTO TEST VALUES ('1998','100008','999991');
INSERT INTO TEST VALUES ('1999','100009','999990');
INSERT INTO TEST VALUES ('2000','100010','999989');
INSERT INTO TEST VALUES ('2001','100011','999988');
COMMIT;
SELECT * FROM TEST;
YEARDATA DATA1 DATA2
-------- ------ ------
1990 100000 999999
1991 100001 999998
1992 100002 999997
1993 100003 999996
1994 100004 999995
1995 100005 999994
1996 100006 999993
1997 100007 999992
1998 100008 999991
1999 100009 999990
2000 100010 999989
2001 100011 999988
12 rows selected.
How to Perform Dynamic PIVOT Using a Procedure
The dynamic PIVOT method is useful in environments where column values change frequently.
(In contrast, it is difficult to write when the number of table rows increases using a static method.)
(In contrast, it is difficult to write when the number of table rows increases using a static method.)
CREATE OR REPLACE PROCEDURE SP_PIVOT(OUT_CURSOR OUT SYS_REFCURSOR) IS SQL_STMT VARCHAR2(30000); V_ROWIDX NUMBER(3) := 0; BEGIN SQL_STMT := 'SELECT '; FOR X IN ( SELECT YEARDATA FROM TEST ORDER BY YEARDATA ) LOOP IF V_ROWIDX = 0 THEN V_ROWIDX := V_ROWIDX + 1; SQL_STMT := SQL_STMT||' MAX(DECODE(YEARDATA,'||X.YEARDATA||',NVL(DATA1,0),'''')) AS "'||X.YEARDATA||'"'; ELSE SQL_STMT := SQL_STMT||', MAX(DECODE(YEARDATA,'||X.YEARDATA||',NVL(DATA1,0),'''')) AS "'||X.YEARDATA||'"'; END IF; END LOOP; SQL_STMT := SQL_STMT||' FROM TEST'; SQL_STMT := SQL_STMT||' UNION ALL SELECT '; V_ROWIDX := 0; FOR Y IN ( SELECT YEARDATA FROM TEST ORDER BY YEARDATA ) LOOP IF V_ROWIDX = 0 THEN V_ROWIDX := V_ROWIDX + 1; SQL_STMT := SQL_STMT||' MAX(DECODE(YEARDATA,'||Y.YEARDATA||',NVL(DATA2,0),'''')) AS "'||Y.YEARDATA||'"'; ELSE SQL_STMT := SQL_STMT||', MAX(DECODE(YEARDATA,'||Y.YEARDATA||',NVL(DATA2,0),'''')) AS "'||Y.YEARDATA||'"'; END IF; END LOOP; SQL_STMT := SQL_STMT||' FROM TEST;'; OPEN OUT_CURSOR FOR(SQL_STMT); DBMS_OUTPUT.PUT_LINE(SQL_STMT); END; / Procedure 'SP_PIVOT' created.
Execute the procedure to dynamically generate the query. (The query syntax can be dynamically generated even when YEARDATA increases.)
SQL> SET SERVEROUTPUT ON SQL> var out_cursor refcursor SQL> exec SP_PIVOT(:out_cursor); SELECT MAX(DECODE(YEARDATA,1990,NVL(DATA1,0),'')) AS "1990", MAX(DECODE(YEARDATA,1991,NVL(DATA1,0),'')) AS "1991", MAX(DECODE(YEARDATA,1992,NVL(DATA1,0),'')) AS "1992", MAX(DECODE(YEARDATA,1993,NVL(DATA1,0),'')) AS "1993", MAX(DECODE(YEARDATA,1994,NVL(DATA1,0),'')) AS "1994", MAX(DECODE(YEARDATA,1995,NVL(DATA1,0),'')) AS "1995", MAX(DECODE(YEARDATA,1996,NVL(DATA1,0),'')) AS "1996", MAX(DECODE(YEARDATA,1997,NVL(DATA1,0),'')) AS "1997", MAX(DECODE(YEARDATA,1998,NVL(DATA1,0),'')) AS "1998", MAX(DECODE(YEARDATA,1999,NVL(DATA1,0),'')) AS "1999", MAX(DECODE(YEARDATA,2000,NVL(DATA1,0),'')) AS "2000", MAX(DECODE(YEARDATA,2001,NVL(DATA1,0),'')) AS "2001" FROM TEST UNION ALL SELECT MAX(DECODE(YEARDATA,1990,NVL(DATA2,0),'')) AS "1990", MAX(DECODE(YEARDATA,1991,NVL(DATA2,0),'')) AS "1991", MAX(DECODE(YEARDATA,1992,NVL(DATA2,0),'')) AS "1992", MAX(DECODE(YEARDATA,1993,NVL(DATA2,0),'')) AS "1993", MAX(DECODE(YEARDATA,1994,NVL(DATA2,0),'')) AS "1994", MAX(DECODE(YEARDATA,1995,NVL(DATA2,0),'')) AS "1995", MAX(DECODE(YEARDATA,1996,NVL(DATA2,0),'')) AS "1996", MAX(DECODE(YEARDATA,1997,NVL(DATA2,0),'')) AS "1997", MAX(DECODE(YEARDATA,1998,NVL(DATA2,0),'')) AS "1998", MAX(DECODE(YEARDATA,1999,NVL(DATA2,0),'')) AS "1999", MAX(DECODE(YEARDATA,2000,NVL(DATA2,0),'')) AS "2000", MAX(DECODE(YEARDATA,2001,NVL(DATA2,0),'')) AS "2001" FROM TEST;
The query execution result is as follows.
SQL> SELECT MAX(DECODE(YEARDATA,1990,NVL(DATA1,0),'')) AS "1990", MAX(DECODE(YEARDATA,1991,NVL(DATA1,0),'')) AS "1991", MAX(DECODE(YEARDATA,1992,NVL(DATA1,0),'')) AS "1992", MAX(DECODE(YEARDATA,1993,NVL(DATA1,0),'')) AS "1993", MAX(DECODE(YEARDATA,1994,NVL(DATA1,0),'')) AS "1994", MAX(DECODE(YEARDATA,1995,NVL(DATA1,0),'')) AS "1995", MAX(DECODE(YEARDATA,1996,NVL(DATA1,0),'')) AS "1996", MAX(DECODE(YEARDATA,1997,NVL(DATA1,0),'')) AS "1997", MAX(DECODE(YEARDATA,1998,NVL(DATA1,0),'')) AS "1998", MAX(DECODE(YEARDATA,1999,NVL(DATA1,0),'')) AS "1999", MAX(DECODE(YEARDATA,2000,NVL(DATA1,0),'')) AS "2000", MAX(DECODE(YEARDATA,2001,NVL(DATA1,0),'')) AS "2001" FROM TEST UNION ALL SELECT MAX(DECODE(YEARDATA,1990,NVL(DATA2,0),'')) AS "1990", MAX(DECODE(YEARDATA,1991,NVL(DATA2,0),'')) AS "1991", MAX(DECODE(YEARDATA,1992,NVL(DATA2,0),'')) AS "1992", MAX(DECODE(YEARDATA,1993,NVL(DATA2,0),'')) AS "1993", MAX(DECODE(YEARDATA,1994,NVL(DATA2,0),'')) AS "1994", MAX(DECODE(YEARDATA,1995,NVL(DATA2,0),'')) AS "1995", MAX(DECODE(YEARDATA,1996,NVL(DATA2,0),'')) AS "1996", MAX(DECODE(YEARDATA,1997,NVL(DATA2,0),'')) AS "1997", MAX(DECODE(YEARDATA,1998,NVL(DATA2,0),'')) AS "1998", MAX(DECODE(YEARDATA,1999,NVL(DATA2,0),'')) AS "1999", MAX(DECODE(YEARDATA,2000,NVL(DATA2,0),'')) AS "2000", MAX(DECODE(YEARDATA,2001,NVL(DATA2,0),'')) AS "2001" FROM TEST; 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ 100000 100001 100002 100003 100004 100005 100006 100007 100008 100009 100010 100011 999999 999998 999997 999996 999995 999994 999993 999992 999991 999990 999989 999988 2 rows selected.