HomeMogDBMogDB StackUqbar
Ecological Tools
Doc Tools
中文
v5.0

Documentation:v5.0

Supported Versions:
Current(v5.0)

Other Versions:
v3.1/
v3.0/
v2.1/
v2.0/
v1.1

Support for PIVOT and UNPIVOT Syntax

Availability

This feature is available since MogDB 5.0.4.

Introduction

This feature is compatible with Oracle's PIVOT and UNPIVOT syntax and functionality.

Benefits

Enhance MogDB compatibility with Oracle to reduce application migration costs.

Description

The PIVOT clause is used to transform the values of specified fields from rows into columns, while the UNPIVOT clause is used to transform the values of specified fields from columns into rows.

Syntax Description

PIVOT

pivot_clause::= PIVOT ( aggregate_function ( expr ) [[AS] alias ][, ...]
    pivot_for_clause
    pivot_in_clause
  )
pivot_for_clause::= FOR (column [, ...])
pivot_in_clause::= IN ({{{ expr | (expr [, ...])} [[AS] alias] [, ...]} | subquery [, ...]})

UNPIVOT

unpivot_clause::= UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
({column | (column [, ...])}
  pivot_for_clause
  unpivot_in_clause
)
pivot_for_clause::= FOR (column [, ...])
unpivot_in_clause::= IN ({column | (column [, ...])} [AS {literal | (literal [, ...])}] [ {column | (column [, ...])} [AS {literal | (literal [, ...])}]])

Constraints

  • PIVOT and UNPIVOT only support query statements.
  • PIVOT and UNPIVOT support regular tables, temporary tables, column store tables, partitioned tables, subqueries, and WITH clauses, etc. They support multiple PIVOTs, multiple UNPIVOTs, joins, and parallel processing.
  • PIVOT and UNPIVOT support CREATE VIEW, CREATE TABLE AS, and SELECT INTO statements.
  • PIVOT supports hashAgg and sortAgg.
  • The PIVOT clause does not support XML.
  • The PIVOT IN clause does not support subqueries and ANY.
  • PIVOT and UNPIVOT do not support vectorization at the moment.
  • PIVOT and UNPIVOT do not support nesting.

Example

PIVOT

# PIVOT usage example with a regular table
MogDB=# create table emp_phone(name varchar2(50), type char, phone varchar2(50));
CREATE TABLE
MogDB=# insert into emp_phone values('aaa', '1', '1234-5678');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '2', '3219-6066');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '5365-9583');
INSERT 0 1
MogDB=# insert into emp_phone values('bbb', '1', '6837-2745');
INSERT 0 1
MogDB=# insert into emp_phone values('bbb', '3', '2649-5820');
INSERT 0 1
MogDB=# insert into emp_phone values('ccc', '1', '5838-9002');
INSERT 0 1
MogDB=# insert into emp_phone values('ccc', '2', '2749-5580');
INSERT 0 1
MogDB=# insert into emp_phone values('ddd', '2', '9876-3453');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '5365-9599');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '1111-9599');
INSERT 0 1
MogDB=# select * from emp_phone pivot(max(phone) for type in (1  home, 2  office, 3 mobile)) order by 1;
 name |   home    |  office   |  mobile
------+-----------+-----------+-----------
 aaa  | 1234-5678 | 3219-6066 | 5365-9599
 bbb  | 6837-2745 |           | 2649-5820
 ccc  | 5838-9002 | 2749-5580 |
 ddd  |           | 9876-3453 |
(4 rows)

# If there is no alias in the in clause, then only the value is used as the column name
MogDB=# select * from emp_phone pivot(max(phone) for type in (1, 2, 3));
 name |     1     |     2     |     3
------+-----------+-----------+-----------
 aaa  | 1234-5678 | 3219-6066 | 5365-9599
 bbb  | 6837-2745 |           | 2649-5820
 ccc  | 5838-9002 | 2749-5580 |
 ddd  |           | 9876-3453 |
(4 rows)

# Delete table
MogDB=# drop table emp_phone;
DROP TABLE

# Example of using PIVOT partition table
MogDB=# create table emp_phone(name varchar2(50), type char, phone varchar2(50))
partition by list(type)
(
  PARTITION p1 VALUES ('1', '2'),
  PARTITION p2 VALUES ('3')
);
CREATE TABLE
MogDB=# insert into emp_phone values('aaa', '1', '1234-5678');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '2', '3219-6066');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '5365-9583');
INSERT 0 1
MogDB=# insert into emp_phone values('bbb', '1', '6837-2745');
INSERT 0 1
MogDB=# insert into emp_phone values('bbb', '3', '2649-5820');
INSERT 0 1
MogDB=# insert into emp_phone values('ccc', '1', '5838-9002');
INSERT 0 1
MogDB=# insert into emp_phone values('ccc', '2', '2749-5580');
INSERT 0 1
MogDB=# insert into emp_phone values('ddd', '2', '9876-3453');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '5365-9599');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '1111-9599');
INSERT 0 1
MogDB=# select * from emp_phone pivot(max(phone) for type in (1  home, 2  office, 3 mobile)) order by 1;
 name |   home    |  office   |  mobile
------+-----------+-----------+-----------
 aaa  | 2234-5678 | 3219-6066 | 5365-9599
 bbb  | 6837-2745 |           | 2649-5820
 ccc  | 5838-9002 | 2749-5580 |
 ddd  |           | 9876-3453 |
(4 rows)

MogDB=# explain(verbose, costs off) select * from emp_phone partition(p1) pivot(max(phone) for type in (1  home, 2  office, 3 mobile)) order by 1;
                                                                                                                                                                              QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
 Sort
   Output: emp_phone.name, (max((CASE WHEN ((emp_phone.type)::bigint = 1) THEN emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((emp_phone.type)::bigint = 2) THEN
 emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((emp_phone.type)::bigint = 3) THEN emp_phone.phone ELSE NULL::character varying END)::text))
   Sort Key: emp_phone.name
   ->  HashAggregate
         Output: emp_phone.name, max((CASE WHEN ((emp_phone.type)::bigint = 1) THEN emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((emp_phone.type)::bigint = 2) T
HEN emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((emp_phone.type)::bigint = 3) THEN emp_phone.phone ELSE NULL::character varying END)::text)
         Group By Key: emp_phone.name
         ->  Partition Iterator
               Output: emp_phone.name, emp_phone.type, emp_phone.phone
               Iterations: 1
               Selected Partitions:  1
               ->  Partitioned Seq Scan on public.emp_phone
                     Output: emp_phone.name, emp_phone.type, emp_phone.phone
(12 rows)

# PIVOT supports join operations
MogDB=# explain (verbose) select * from emp_phone pivot(max(phone) for type in (1 as home, 2 as office, 3 as mobile)) as p1, emp_phone pivot(max(phone) for type in (1 as home, 2 as office, 3 as mobile)) as p2 where p1.name=p2.name;

                                                 QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------

 Hash Join  (cost=47.36..54.11 rows=200 distinct=[200, 200] width=428)
   Output: public.emp_phone.name, (max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public.emp_p
hone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phone.phone ELSE NULL::c
haracter varying END)::text)), public.emp_phone.name, (max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE
 WHEN ((public.emp_phone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phon
e.phone ELSE NULL::character varying END)::text))
   Hash Cond: ((public.emp_phone.name)::text = (public.emp_phone.name)::text)
   ->  HashAggregate  (cost=20.43..22.43 rows=200 width=340)
         Output: public.emp_phone.name, max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((public.em
p_phone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phone.phone ELSE NULL::
character varying END)::text)
         Group By Key: public.emp_phone.name
         ->  Partition Iterator  (cost=0.00..12.98 rows=298 width=244)
               Output: public.emp_phone.name, public.emp_phone.type, public.emp_phone.phone
               Iterations: 2
               Selected Partitions:  1..2
               ->  Partitioned Seq Scan on public.emp_phone  (cost=0.00..12.98 rows=298 width=244)
                     Output: public.emp_phone.name, public.emp_phone.type, public.emp_phone.phone
   ->  Hash  (cost=24.43..24.43 rows=200 width=214)
         Output: public.emp_phone.name, (max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public
.emp_phone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phone.phone ELSE N
ULL::character varying END)::text))
         ->  HashAggregate  (cost=20.43..22.43 rows=200 width=340)
               Output: public.emp_phone.name, max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((pub
lic.emp_phone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phone.phone ELSE
NULL::character varying END)::text)
               Group By Key: public.emp_phone.name
               ->  Partition Iterator  (cost=0.00..12.98 rows=298 width=244)
                     Output: public.emp_phone.name, public.emp_phone.type, public.emp_phone.phone
                     Iterations: 2
                     Selected Partitions:  1..2
                     ->  Partitioned Seq Scan on public.emp_phone  (cost=0.00..12.98 rows=298 width=244)
                           Output: public.emp_phone.name, public.emp_phone.type, public.emp_phone.phone
(23 rows)

# The pivot_for clause supports multiple columns.
MogDB=# create table cust_sales_category(location varchar(20),prod_category varchar(50),customer_id int,sale_amount int);
CREATE TABLE
MogDB=# insert into cust_sales_category (location,prod_category,customer_id,sale_amount) values
MogDB-# ('north','furniture',2,875),
MogDB-# ('south','electronics',2,378),
MogDB-# ('east','gardening',4,136),
MogDB-# ('west','electronics',3,236),
MogDB-# ('central','furniture',3,174),
MogDB-# ('north','electronics',1,729),
MogDB-# ('east','gardening',2,147),
MogDB-# ('west','electronics',3,200),
MogDB-# ('north','furniture',4,987),
MogDB-# ('central','gardening',4,584),
MogDB-# ('south','electronics',3,714),
MogDB-# ('east','furniture',1,192),
MogDB-# ('west','gardening',3,946),
MogDB-# ('east','electronics',4,649),
MogDB-# ('south','furniture',2,503),
MogDB-# ('north','electronics',1,399),
MogDB-# ('central','gardening',3,259),
MogDB-# ('east','electronics',3,407),
MogDB-# ('west','furniture',1,545);
INSERT 0 19
MogDB=# SELECT * FROM (SELECT location, prod_category, customer_id, sale_amount FROM cust_sales_category) PIVOT (SUM(sale_amount) FOR (customer_id, prod_category)IN ((1, 'furniture') AS furn1, (2, 'furniture') AS furn2, (1, 'electronics') AS elec1, (2, 'electronics') AS elec2)) order by 1;
 location | furn1 | furn2 | elec1 | elec2
----------+-------+-------+-------+-------
 central  |       |       |       |
 east     |   192 |       |       |
 north    |       |   875 |  1128 |
 south    |       |   503 |       |   378
 west     |   545 |       |       |
(5 rows)

MogDB=# explain(verbose, analyze) SELECT * FROM (SELECT location, prod_category, customer_id, sale_amount FROM cust_sales_category) PIVOT (SUM(sale_amount) FOR (customer_id, prod_category)IN ((1, 'furniture') AS furn1, (2, 'furniture') AS furn2, (1, 'electronics') AS elec1, (2, 'electronics') AS elec2)) order by 1;
                                                                                                                                                                                   QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=37.88..38.38 rows=200 width=90) (actual time=0.075..0.076 rows=5 loops=1)
   Output: cust_sales_category.location, (sum(CASE WHEN ((cust_sales_category.customer_id = 1) AND ((cust_sales_category.prod_category)::text = 'furniture'::text)) THEN cust_sales_category.sale_amount ELSE NULL
::integer END)), (sum(CASE WHEN ((cust_sales_category.customer_id = 2) AND ((cust_sales_category.prod_category)::text = 'furniture'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END)), (sum(CA
SE WHEN ((cust_sales_category.customer_id = 1) AND ((cust_sales_category.prod_category)::text = 'electronics'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END)), (sum(CASE WHEN ((cust_sales_c
ategory.customer_id = 2) AND ((cust_sales_category.prod_category)::text = 'electronics'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END))
   Sort Key: cust_sales_category.location
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=26.23..28.23 rows=200 width=216) (actual time=0.060..0.060 rows=5 loops=1)
         Output: cust_sales_category.location, sum(CASE WHEN ((cust_sales_category.customer_id = 1) AND ((cust_sales_category.prod_category)::text = 'furniture'::text)) THEN cust_sales_category.sale_amount ELSE
 NULL::integer END), sum(CASE WHEN ((cust_sales_category.customer_id = 2) AND ((cust_sales_category.prod_category)::text = 'furniture'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END), sum(C
ASE WHEN ((cust_sales_category.customer_id = 1) AND ((cust_sales_category.prod_category)::text = 'electronics'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END), sum(CASE WHEN ((cust_sales_ca
tegory.customer_id = 2) AND ((cust_sales_category.prod_category)::text = 'electronics'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END)
         Group By Key: cust_sales_category.location
         ->  Seq Scan on public.cust_sales_category  (cost=0.00..13.82 rows=382 width=184) (actual time=0.019..0.022 rows=19 loops=1)
               Output: cust_sales_category.location, cust_sales_category.customer_id, cust_sales_category.prod_category, cust_sales_category.sale_amount
 Total runtime: 0.179 ms
(10 rows)

# pivot_for supports with clauses
MogDB=# with a as (
MogDB(# select 'Jack' Name  ,'sex' Key,'male' Value union all
MogDB(# select 'Jack'  ,'country','USA' union all
MogDB(# select 'Jack'  ,'hobby','sing' union all
MogDB(# select 'Jack'  ,'age','19' union all
MogDB(# select 'Bob' ,'country','UK' union all
MogDB(# select 'Bob' ,'age','20' union all
MogDB(# select 'Bob' ,'weight','70' union all
MogDB(# select 'Maria' ,'sex','female' union all
MogDB(# select 'Maria' ,'weight','50')
MogDB-# select * from a pivot (max(value) for key in ('sex' sex,'country' country,'hobby' hobby,'age' age,'weight' weight)) order by 1,2;
 name  |  sex   | country | hobby | age | weight
-------+--------+---------+-------+-----+--------
 Bob   |        | UK      |       | 20  | 70
 Jack  | male   | USA     | sing  | 19  |
 Maria | female |         |       |     | 50
(3 rows)

# PIVOT supports multiple aggregation functions
MogDB=# create table t_demo(id int, name text, nums int);
CREATE TABLE
MogDB=# insert into t_demo values(1,'aa',1000),(2,'aa',2000),(3,'aa',4000),(4,'bb',5000),(5,'bb',3000),(6,'cc',3500),(7,'dd',4200),(8,'dd',5500);
INSERT 0 8
MogDB=# select * from (select name, nums from t_demo) pivot (sum(nums) total,min(nums) min for name in ('aa' as apple, 'bb' as orange, 'cc' as grape, 'dd' as mango));
 apple_total | apple_min | orange_total | orange_min | grape_total | grape_min | mango_total | mango_min
-------------+-----------+--------------+------------+-------------+-----------+-------------+-----------
        7000 |      1000 |         8000 |       3000 |        3500 |      3500 |        9700 |      4200
(1 row)

# PIVOT supports multi-column and multi-aggregate functions
MogDB=# create table tab1(type varchar2(50), weight int, height int);
CREATE TABLE
MogDB=# insert into tab1 values('A',50,10),('A',60,12),('B',40,8),('C',30,15);
INSERT 0 4
MogDB=# select * from tab1 pivot (count(type) as ct, sum(weight) as wt, sum(height) as ht for type in ('A' as A, 'B' as B, 'C' as C));
 a_ct | a_wt | a_ht | b_ct | b_wt | b_ht | c_ct | c_wt | c_ht
------+------+------+------+------+------+------+------+------
    2 |  110 |   22 |    1 |   40 |    8 |    1 |   30 |   15
(1 row)

# PIVOT aggregation function supports expr
MogDB=# select * from emp_phone pivot(max(phone||'xxx') for type in (1  home, 2  office, 3 mobile)) order by 1;
 name |     home     |    office    |    mobile
------+--------------+--------------+--------------
 aaa  | 2234-5678xxx | 3219-6066xxx | 5365-9599xxx
 bbb  | 6837-2745xxx |              | 2649-5820xxx
 ccc  | 5838-9002xxx | 2749-5580xxx |
 ddd  |              | 9876-3453xxx |
(4 rows)

MogDB=# explain(verbose, analyze) select * from emp_phone pivot(max(phone||'xxx') for type in (1  home, 2  office, 3 mobile)) order by 1;
                                              QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=34.31..34.81 rows=200 width=214) (actual time=0.088..0.088 rows=4 loops=1)
   Output: emp_phone.name, (max(CASE WHEN ((emp_phone.type)::bigint = 1) THEN ((emp_phone.phone)::text || 'xxx'::text) ELSE NULL::text END)), (max(CASE WHEN ((emp_phone.type)::bigint = 2) THEN ((emp_phone.phone
)::text || 'xxx'::text) ELSE NULL::text END)), (max(CASE WHEN ((emp_phone.type)::bigint = 3) THEN ((emp_phone.phone)::text || 'xxx'::text) ELSE NULL::text END))
   Sort Key: emp_phone.name
   Sort Method: quicksort  Memory: 25kB
   ->  HashAggregate  (cost=22.67..24.67 rows=200 width=340) (actual time=0.073..0.075 rows=4 loops=1)
         Output: emp_phone.name, max(CASE WHEN ((emp_phone.type)::bigint = 1) THEN ((emp_phone.phone)::text || 'xxx'::text) ELSE NULL::text END), max(CASE WHEN ((emp_phone.type)::bigint = 2) THEN ((emp_phone.ph
one)::text || 'xxx'::text) ELSE NULL::text END), max(CASE WHEN ((emp_phone.type)::bigint = 3) THEN ((emp_phone.phone)::text || 'xxx'::text) ELSE NULL::text END)
         Group By Key: emp_phone.name
         ->  Partition Iterator  (cost=0.00..12.98 rows=298 width=244) (actual time=0.012..0.022 rows=11 loops=1)
               Output: emp_phone.name, emp_phone.type, emp_phone.phone
               Iterations: 2
               Selected Partitions:  1..2
               ->  Partitioned Seq Scan on public.emp_phone  (cost=0.00..12.98 rows=298 width=244) (actual time=0.007..0.010 rows=11 loops=2)
                     Output: emp_phone.name, emp_phone.type, emp_phone.phone
 Total runtime: 0.201 ms
(14 rows)

# PIVOT supports create table as
MogDB=# create table test1 as select * from emp_phone pivot(max(phone) for type in (1  home, 2  office, 3 mobile)) order by 1;
INSERT 0 4
MogDB=# select * from test1;
 name |   home    |  office   |  mobile
------+-----------+-----------+-----------
 aaa  | 2234-5678 | 3219-6066 | 5365-9599
 bbb  | 6837-2745 |           | 2649-5820
 ccc  | 5838-9002 | 2749-5580 |
 ddd  |           | 9876-3453 |
(4 rows)

# PIVOT supports select into
MogDB=# select * into test2 from emp_phone pivot(max(phone) for type in (1  home, 2  office, 3 mobile)) order by 1;
INSERT 0 4
MogDB=# select * from test2;
 name |   home    |  office   |  mobile
------+-----------+-----------+-----------
 aaa  | 2234-5678 | 3219-6066 | 5365-9599
 bbb  | 6837-2745 |           | 2649-5820
 ccc  | 5838-9002 | 2749-5580 |
 ddd  |           | 9876-3453 |
(4 rows)

# PIVOT supports view
MogDB=# create view tv1 as select * from emp_phone pivot(max(phone) for type in (1  home, 2  office, 3 mobile));
CREATE VIEW
MogDB=# \d+ tv1;
                          View "public.tv1"
 Column |         Type          | Modifiers | Storage  | Description
--------+-----------------------+-----------+----------+-------------
 name   | character varying(50) |           | extended |
 home   | text                  |           | extended |
 office | text                  |           | extended |
 mobile | text                  |           | extended |
View definition:
 SELECT  *
   FROM ( SELECT emp_phone.name,
            max(
                CASE
                    WHEN emp_phone.type::bigint = 1 THEN emp_phone.phone
                    ELSE NULL::character varying
                END::text) AS home,
            max(
                CASE
                    WHEN emp_phone.type::bigint = 2 THEN emp_phone.phone
                    ELSE NULL::character varying
                END::text) AS office,
            max(
                CASE
                    WHEN emp_phone.type::bigint = 3 THEN emp_phone.phone
                    ELSE NULL::character varying
                END::text) AS mobile
           FROM emp_phone
          GROUP BY emp_phone.name) unnamed_pivot;
    
MogDB=# select * from tv1;
 name |   home    |  office   |  mobile
------+-----------+-----------+-----------
 aaa  | 2234-5678 | 3219-6066 | 5365-9599
 bbb  | 6837-2745 |           | 2649-5820
 ccc  | 5838-9002 | 2749-5580 |
 ddd  |           | 9876-3453 |
(4 rows)

UNPIVOT

# UNPIVOT usage examples
MogDB=# create table emp_phone1(name varchar2(50), home varchar2(50), office varchar2(50), mobile varchar2(50));
CREATE TABLE
MogDB=# insert into emp_phone1 values('aaa','1234-5678','3219-6066','5365-9583');
INSERT 0 1
MogDB=# insert into emp_phone1 values('bbb','5838-9002','2749-5580','');
INSERT 0 1
MogDB=# insert into emp_phone1 values('ccc','','9876-3453','');
INSERT 0 1
MogDB=# insert into emp_phone1 values('ddd','6837-2745','','2649-5820');
INSERT 0 1
MogDB=# insert into emp_phone1 values('eee','','','2649-5820');
INSERT 0 1
# In unpivot_in, the privacy conversion of the in type uses the privacy type conversion of the default list
MogDB=# select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3));
 name | type |   phone
------+------+-----------
 aaa  |    1 | 1234-5678
 aaa  |    2 | 3219-6066
 aaa  |    3 | 5365-9583
 bbb  |    1 | 5838-9002
 bbb  |    2 | 2749-5580
 ccc  |    2 | 9876-3453
 ddd  |    1 | 6837-2745
 ddd  |    3 | 2649-5820
 eee  |    3 | 2649-5820
(9 rows)

MogDB=# explain(verbose, analyze) select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3));
                                                      QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
 Unpivot  (cost=0.00..12.85 rows=487 width=154) (actual time=0.010..0.012 rows=9 loops=1)
   Output: unnamed_unpivot.name, unnamed_unpivot.type, unnamed_unpivot.phone
   Project 1: emp_phone1.name, 1, emp_phone1.home
   Project 2: emp_phone1.name, 2, emp_phone1.office
   Project 3: emp_phone1.name, 3, emp_phone1.mobile
   Filter 1: (emp_phone1.home IS NOT NULL)
   Filter 2: (emp_phone1.office IS NOT NULL)
   Filter 3: (emp_phone1.mobile IS NOT NULL)
   ->  Seq Scan on public.emp_phone1  (cost=0.00..11.63 rows=163 width=472) (actual time=0.007..0.007 rows=5 loops=1)
         Output: emp_phone1.name, emp_phone1.home, emp_phone1.office, emp_phone1.mobile
 Total runtime: 0.067 ms
(11 rows)

MogDB=# select * from emp_phone1 unpivot include nulls (phone for type in (home as 1, office as 2, mobile as 3));
 name | type |   phone
------+------+-----------
 aaa  |    1 | 1234-5678
 aaa  |    2 | 3219-6066
 aaa  |    3 | 5365-9583
 bbb  |    1 | 5838-9002
 bbb  |    2 | 2749-5580
 bbb  |    3 |
 ccc  |    1 |
 ccc  |    2 | 9876-3453
 ccc  |    3 |
 ddd  |    1 | 6837-2745
 ddd  |    2 |
 ddd  |    3 | 2649-5820
 eee  |    1 |
 eee  |    2 |
 eee  |    3 | 2649-5820
(15 rows)

# UNPIVOT supports parallelism
MogDB=# set query_dop = 4;
SET
MogDB=# set smp_thread_cost = 0;
SET
MogDB=# explain(verbose,analyze) select * from emp_phone1 unpivot include nulls (phone for type in (home as 1, office as 2, mobile as 3));
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Streaming(type: LOCAL GATHER dop: 1/4)  (cost=0.00..23.04 rows=489 width=154) (actual time=[18.733,32.127]..[18.733,32.127], rows=15)
   Output: unnamed_unpivot.name, unnamed_unpivot.type, unnamed_unpivot.phone
   ->  Unpivot  (cost=0.00..3.21 rows=489 width=154) (actual time=[0.001,0.001]..[0.008,0.015], rows=15)
         Output: unnamed_unpivot.name, unnamed_unpivot.type, unnamed_unpivot.phone
         Project 1: emp_phone1.name, 1, emp_phone1.home
         Project 2: emp_phone1.name, 2, emp_phone1.office
         Project 3: emp_phone1.name, 3, emp_phone1.mobile
         ->  Seq Scan on public.emp_phone1  (cost=0.00..2.91 rows=163 width=472) (actual time=[0.000,0.000]..[0.005,0.006], rows=5)
               Output: emp_phone1.name, emp_phone1.home, emp_phone1.office, emp_phone1.mobile
 Total runtime: 33.168 ms
(10 rows)

# UNPIVOT supports join
MogDB=# explain(verbose,analyze) select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3)) as p1, emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3)) as p2 where p1.name=p2.name;
                                    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=18.94..46.08 rows=1186 distinct=[200, 200] width=308) (actual time=0.162..0.170 rows=19 loops=1)
   Output: p1.name, p1.type, p1.phone, p2.name, p2.type, p2.phone
   Hash Cond: ((p1.name)::text = (p2.name)::text)
   ->  Unpivot  (cost=0.00..12.85 rows=487 width=154) (actual time=0.009..0.013 rows=9 loops=1)
         Output: p1.name, p1.type, p1.phone
         Project 1: public.emp_phone1.name, 1, public.emp_phone1.home
         Project 2: public.emp_phone1.name, 2, public.emp_phone1.office
         Project 3: public.emp_phone1.name, 3, public.emp_phone1.mobile
         Filter 1: (public.emp_phone1.home IS NOT NULL)
         Filter 2: (public.emp_phone1.office IS NOT NULL)
         Filter 3: (public.emp_phone1.mobile IS NOT NULL)
         ->  Seq Scan on public.emp_phone1  (cost=0.00..11.63 rows=163 width=472) (actual time=0.006..0.006 rows=5 loops=1)
               Output: public.emp_phone1.name, public.emp_phone1.home, public.emp_phone1.office, public.emp_phone1.mobile
   ->  Hash  (cost=17.72..17.72 rows=487 width=154) (actual time=0.024..0.024 rows=9 loops=1)
         Output: p2.name, p2.type, p2.phone
          Buckets: 32768  Batches: 1  Memory Usage: 257kB
         ->  Unpivot  (cost=0.00..12.85 rows=487 width=154) (actual time=0.001..0.014 rows=9 loops=1)
               Output: p2.name, p2.type, p2.phone
               Project 1: public.emp_phone1.name, 1, public.emp_phone1.home
               Project 2: public.emp_phone1.name, 2, public.emp_phone1.office
               Project 3: public.emp_phone1.name, 3, public.emp_phone1.mobile
               Filter 1: (public.emp_phone1.home IS NOT NULL)
               Filter 2: (public.emp_phone1.office IS NOT NULL)
               Filter 3: (public.emp_phone1.mobile IS NOT NULL)
               ->  Seq Scan on public.emp_phone1  (cost=0.00..11.63 rows=163 width=472) (actual time=0.001..0.013 rows=5 loops=1)
                     Output: public.emp_phone1.name, public.emp_phone1.home, public.emp_phone1.office, public.emp_phone1.mobile
 Total runtime: 0.290 ms
(27 rows)

MogDB=# explain(verbose,analyze) select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3)) as p1, emp_phone as p2 where p1.name=p2.name;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=16.71..38.64 rows=726 distinct=[200, 200] width=398) (actual time=0.187..0.194 rows=26 loops=1)
   Output: p1.name, p1.type, p1.phone, p2.name, p2.type, p2.phone
   Hash Cond: ((p1.name)::text = (p2.name)::text)
   ->  Unpivot  (cost=0.00..12.85 rows=487 width=154) (actual time=0.009..0.011 rows=9 loops=1)
         Output: p1.name, p1.type, p1.phone
         Project 1: emp_phone1.name, 1, emp_phone1.home
         Project 2: emp_phone1.name, 2, emp_phone1.office
         Project 3: emp_phone1.name, 3, emp_phone1.mobile
         Filter 1: (emp_phone1.home IS NOT NULL)
         Filter 2: (emp_phone1.office IS NOT NULL)
         Filter 3: (emp_phone1.mobile IS NOT NULL)
         ->  Seq Scan on public.emp_phone1  (cost=0.00..11.63 rows=163 width=472) (actual time=0.005..0.005 rows=5 loops=1)
               Output: emp_phone1.name, emp_phone1.home, emp_phone1.office, emp_phone1.mobile
   ->  Hash  (cost=12.98..12.98 rows=298 width=244) (actual time=0.040..0.040 rows=11 loops=1)
         Output: p2.name, p2.type, p2.phone
          Buckets: 32768  Batches: 1  Memory Usage: 257kB
         ->  Partition Iterator  (cost=0.00..12.98 rows=298 width=244) (actual time=0.025..0.033 rows=11 loops=1)
               Output: p2.name, p2.type, p2.phone
               Iterations: 2
               Selected Partitions:  1..2
               ->  Partitioned Seq Scan on public.emp_phone p2  (cost=0.00..12.98 rows=298 width=244) (actual time=0.005..0.005 rows=11 loops=2)
                     Output: p2.name, p2.type, p2.phone
 Total runtime: 0.299 ms
(23 rows)

# UNPIVOT supports multiple columns
MogDB=# create table emp_phone2(name varchar2(50), home varchar2(50), office varchar2(50), mobile varchar2(50), extra varchar2(50));
CREATE TABLE
MogDB=# insert into emp_phone2 values('aaa','1234-5678','3219-6066','5365-9583','11111');
INSERT 0 1
MogDB=# insert into emp_phone2 values('bbb','5838-9002','2749-5580','','22222');
INSERT 0 1
MogDB=# insert into emp_phone2 values('ccc','','9876-3453','','333333');
INSERT 0 1
MogDB=# insert into emp_phone2 values('ddd','6837-2745','','2649-5820','44444');
INSERT 0 1
MogDB=# insert into emp_phone2 values('eee','','','2649-5820','44444');
INSERT 0 1
MogDB=# select * from emp_phone2 unpivot((phone,phone1) for (type1,type2) in ((home,office) as (1,11), (mobile,extra) as (3,33)));
 name | type1 | type2 |   phone   |  phone1
------+-------+-------+-----------+-----------
 aaa  |     1 |    11 | 1234-5678 | 3219-6066
 aaa  |     3 |    33 | 5365-9583 | 11111
 bbb  |     1 |    11 | 5838-9002 | 2749-5580
 bbb  |     3 |    33 |           | 22222
 ccc  |     1 |    11 |           | 9876-3453
 ccc  |     3 |    33 |           | 333333
 ddd  |     1 |    11 | 6837-2745 |
 ddd  |     3 |    33 | 2649-5820 | 44444
 eee  |     3 |    33 | 2649-5820 | 44444
(9 rows)

MogDB=# explain(verbose,analyze) select * from emp_phone2 unpivot((phone,phone1) for (type1,type2) in ((home,office) as (1,11), (mobile,extra) as (3,33)));
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Unpivot  (cost=0.00..11.98 rows=264 width=190) (actual time=0.009..0.012 rows=9 loops=1)
   Output: unnamed_unpivot.name, unnamed_unpivot.type1, unnamed_unpivot.type2, unnamed_unpivot.phone, unnamed_unpivot.phone1
   Project 1: emp_phone2.name, 1, 11, emp_phone2.home, emp_phone2.office
   Project 2: emp_phone2.name, 3, 33, emp_phone2.mobile, emp_phone2.extra
   Filter 1: ((emp_phone2.home IS NOT NULL) OR (emp_phone2.office IS NOT NULL))
   Filter 2: ((emp_phone2.mobile IS NOT NULL) OR (emp_phone2.extra IS NOT NULL))
   ->  Seq Scan on public.emp_phone2  (cost=0.00..11.32 rows=132 width=590) (actual time=0.006..0.007 rows=5 loops=1)
         Output: emp_phone2.name, emp_phone2.home, emp_phone2.office, emp_phone2.mobile, emp_phone2.extra
 Total runtime: 0.078 ms
(9 rows)

# UNPIVOT supports the with clause
MogDB=# with t as (select 0 a,1 b,2 c,3 d) select * from t unpivot (val for col in (A,B,C,D));
 col | val
-----+-----
 a   |   0
 b   |   1
 c   |   2
 d   |   3
(4 rows)

MogDB=# explain(verbose,analyze) with t as (select 0 a,1 b,2 c,3 d) select * from t unpivot (val for col in (A,B,C,D));
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Unpivot  (cost=0.00..0.02 rows=1 width=36) (actual time=0.004..0.005 rows=4 loops=1)
   Output: unnamed_unpivot.col, unnamed_unpivot.val
   Project 1: 'a'::text, (0)
   Project 2: 'b'::text, (1)
   Project 3: 'c'::text, (2)
   Project 4: 'd'::text, (3)
   Filter 1: ((0) IS NOT NULL)
   Filter 2: ((1) IS NOT NULL)
   Filter 3: ((2) IS NOT NULL)
   Filter 4: ((3) IS NOT NULL)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
         Output: 0, 1, 2, 3
 Total runtime: 0.047 ms
(13 rows)

# UNPIVOT supports view
MogDB=# create view tv2 as select * from emp_phone2 unpivot include nulls((phone,phone1) for (type1,type2) in ((home,office) as (1,11), (mobile,extra) as (3,33)));
CREATE VIEW
MogDB=# \d+ tv2;
                          View "public.tv2"
 Column |         Type          | Modifiers | Storage  | Description
--------+-----------------------+-----------+----------+-------------
 name   | character varying(50) |           | extended |
 type1  | integer               |           | plain    |
 type2  | integer               |           | plain    |
 phone  | character varying     |           | extended |
 phone1 | character varying     |           | extended |
View definition:
 SELECT  *
   FROM emp_phone2 UNPIVOT INCLUDE NULLS ((phone,phone1) FOR (type1,type2) IN ((home,office) AS (1,11),(mobile,extra) AS (3,33)));
MogDB=# select * from tv2;
 name | type1 | type2 |   phone   |  phone1
------+-------+-------+-----------+-----------
 aaa  |     1 |    11 | 1234-5678 | 3219-6066
 aaa  |     3 |    33 | 5365-9583 | 11111
 bbb  |     1 |    11 | 5838-9002 | 2749-5580
 bbb  |     3 |    33 |           | 22222
 ccc  |     1 |    11 |           | 9876-3453
 ccc  |     3 |    33 |           | 333333
 ddd  |     1 |    11 | 6837-2745 |
 ddd  |     3 |    33 | 2649-5820 | 44444
 eee  |     1 |    11 |           |
 eee  |     3 |    33 | 2649-5820 | 44444
(10 rows)

SELECT

HOME

Issue
Copyright © 2011-2024 www.enmotech.com All rights reserved.