Skip to main content

Search

Welcome to Tibero GTS!

How to Perform Data PIVOT Using SUM(DECODE()) Syntax

Document Type | Technical Information

CategoryㅣAdministration

Applicable Product Version | Tibero 6

Document Number | TADTI078

 

Overview

There are three methods to perform data PIVOT as follows.
 
  1. Pivot using SUM(DECODE()) syntax
  2. Dynamic pivot using procedures
  3. Pivot using PIVOT syntax
 
This document explains the method of data PIVOT processing using SUM(DECODE()) syntax.
Note
The test environment used Tibero 6 (DB 6.0 FS06_CS_1703) version environment.
 

Method

Create Test Table

CREATE TABLE TEST( 
		YEARDATA VARCHAR(4), 
		DATA1    VARCHAR(6), 
		DATA2    VARCHAR(6)
);

 

Insert Data

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.

 

SUM(DECODE()) Syntax for Data PIVOT

  1. Perform PIVOT up to the third row of the first column and display the output.
SELECT
	SUM(DECODE(YEARDATA,'1990',NVL(DATA1,0),'')) AS "1990" 
	, SUM(DECODE(YEARDATA,'1991',NVL(DATA1,0),'')) AS "1991" 
	, SUM(DECODE(YEARDATA,'1992',NVL(DATA1,0),'')) AS "1992"
FROM TEST;

1990       1991       1992 
---------- ---------- ---------- 
100000     100001     100002 

1 row selected.

 

2. Perform PIVOT including the second column as well. 
    At this time, add the second column part using UNION.

SELECT
		SUM(DECODE(YEARDATA,'1990',NVL(DATA1,0),'')) AS "1990"
	, SUM(DECODE(YEARDATA,'1991',NVL(DATA1,0),'')) AS "1991" 
	, SUM(DECODE(YEARDATA,'1992',NVL(DATA1,0),'')) AS "1992" 
	FROM TEST
UNION
SELECT
		SUM(DECODE(YEARDATA,'1990',NVL(DATA2,0),'')) AS "1990" 
	, SUM(DECODE(YEARDATA,'1991',NVL(DATA2,0),'')) AS "1991" 
	, SUM(DECODE(YEARDATA,'1992',NVL(DATA2,0),'')) AS "1992" 
	FROM TEST;
	
1990       1991       1992 
---------- ---------- ---------- 
100000     100001     100002 
999999     999998     999997 

2 rows selected