Skip to main content
📓 Tibero Online Manual Click here 📓

Search

Welcome to Tibero GTS!

How to Perform Dynamic PIVOT Using a Procedure

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.
 
  1. PIVOT using SUM(DECODE()) syntax
  2. Dynamic PIVOT using a procedure
  3. 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.
Note
The 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.)
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.

 

 

 

Related to