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.
- Pivot using the SUM(DECODE()) clause
- Dynamic pivot using a procedure
- 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.
NoteThe 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.