Skip to main content
📓 Tibero Online Manual Click here 📓

Search

Welcome to Tibero GTS!

How to Perform Data PIVOT Using the PIVOT Clause

Document Type | Technical Information

CategoryㅣAdministration

Applicable Product Version | Tibero 6

Document Number | TADTI080

 

Overview

There are three methods to perform data PIVOT as follows.
 
  1. Pivot using the SUM(DECODE()) clause
  2. Dynamic pivot using a procedure
  3. Pivot using the PIVOT clause
 
This document explains how to perform data PIVOT using the PIVOT clause.
  • The PIVOT clause is a dedicated pivot feature provided in SQL.
  • It can be included in a SELECT statement to convert row data into column data.
Note
The test environment used Tibero 6 (DB 6.0 FS06_CS_1703) version.

 

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.

 

Example of Performing Data PIVOT Using the PIVOT Clause

SELECT ~ FROM ~ PIVOT ~ query syntax is used to perform this.
SELECT *
	FROM ( SELECT YEARDATA, DATA1 FROM TEST )
	PIVOT (MAX(DATA1) FOR YEARDATA IN ('1990' AS "1990", '1991' AS "1991", '1992' AS "1992"
	, '1993' AS "1993"))
UNION ALL
	SELECT *
		FROM ( SELECT YEARDATA, DATA2 FROM TEST )
		PIVOT (MAX(DATA2) FOR YEARDATA IN ('1990' AS "1990", '1991' AS "1991", '1992' AS "1992",
 '1993' AS "1993"));

1990   1991   1992   1993 
------ ------ ------ ------
100000 100001 100002 100003 
999999 999998 999997 999996 

2 rows selected.