Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-3.x)/ SQL Optimization/ Introduction to the SQL Execution Plan/ Operators/ Operators/ Unpivot
Updated on 2025-03-13 GMT+08:00
View PDF
Share

Unpivot

Description

Transpose operator, which is used to convert rows into columns. This operation can convert data in multiple columns in a table into two columns. One column is the column name in the original table, and the other column is the corresponding value.

Typical Scenarios

Query the table that uses unpivot transposition.

Examples

Example: Query the table that uses unpivot transposition. 
-- The UNPIVOT syntax can be used only in the O-compatible database.
gaussdb=# CREATE DATABASE ora_test WITH DBCOMPATIBILITY = 'ORA';
gaussdb=# \c ora_test 
gaussdb=# SET enable_fast_query_shipping=off; 

-- Prepare data.
ora_test=# DROP TABLE IF EXISTS t1;
ora_test=# CREATE TABLE t1 (id int, number int, grade int);
CREATE TABLE
ora_test=# INSERT INTO t1 VALUES(generate_series(1,100), 1, 2);
INSERT 0 100

-- Execution result.
ora_test=# EXPLAIN SELECT *  FROM t1 UNPIVOT (v1 FOR v2 in (id,number,grade));
                                          oracle
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=1.88..16.24 rows=60 width=36)
   Node/s: All datanodes
   ->  Subquery Scan on __unnamed_unpivot_subquery__  (cost=0.00..13.43 rows=60 width=36)
         Filter: (__unnamed_unpivot_subquery__.v1 IS NOT NULL)
         ->  Unpivot  (cost=0.00..13.13 rows=60 width=36)
               ->  Seq Scan on t1  (cost=0.00..13.13 rows=20 width=12)
(6 rows)

-- Drop.
ora_test=# DROP TABLE t1;
ora_test=# \c gaussdb
gaussdb=# DROP DATABASE ora_test;
Parent topic: Operators