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 for transposition.

Examples

Query the table that uses unpivot for transposition.

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

-- Execution result.
gaussdb=# EXPLAIN SELECT *  FROM t1 UNPIVOT (v1 FOR v2 in (id,number,grade));
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Subquery Scan on __unnamed_unpivot_subquery__  (cost=0.00..87.80 rows=5806 width=36)
   Filter: (__unnamed_unpivot_subquery__.v1 IS NOT NULL)
   ->  Unpivot  (cost=0.00..29.45 rows=5835 width=36)
         ->  Seq Scan on t1  (cost=0.00..29.45 rows=1945 width=12)
(4 rows) 

-- Drop.
gaussdb=# DROP TABLE t1;

In the preceding example, the output of the unpivot operator is as follows.