PolarDB for PostgreSQL(Compatible with Oracle) supports the PIVOT syntax. You can obtain cross table results for all your queries by using PIVOT. The result of a cross table query has more columns and fewer rows than that of a general query.

Syntax

SELECT ....
FROM <table-expr>
   PIVOT
     (
      aggregate-function(<column>) AS <alias>
      FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>)
        ) AS <alias>
WHERE .....

Examples

Create a sample database.

CREATE TABLE SalesOrder
(
  shop VARCHAR(50),
  product VARCHAR(50),
  sales INT
);
insert into SalesOrder values('shopA', 'A', 10);
insert into SalesOrder values('shopA', 'B', 23);
insert into SalesOrder values('shopA', 'B', 63);
insert into SalesOrder values('shopA', 'B', 75);
insert into SalesOrder values('shopA', 'C', 39);
insert into SalesOrder values('shopB', 'A', 25);
insert into SalesOrder values('shopB', 'A', 43);
insert into SalesOrder values('shopB', 'C', 86);
insert into SalesOrder values('shopB', 'C', 33);
insert into SalesOrder values('shopC', 'B', 93);
insert into SalesOrder values('shopC', 'B', 92);
insert into SalesOrder values('shopC', 'B', 27);
insert into SalesOrder values('shopC', 'B', 6);
insert into SalesOrder values('shopC', 'C', 79);
  • Execute a general query. 
    select * from SalesOrder pivot(sum(sales) for product in ('A', 'B'));

    The following result is returned:

     shop  | 'A' | 'B' 
-------+-----+-----
 shopC |     | 218
 shopB |  68 |    
 shopA |  10 | 161
(3 rows)
  • Execute a cross table query. 
    select *
from (select shop, product, sales
      from SalesOrder
      where sales>10)
pivot(sum(sales) for product in ('A', 'B'));

    The following result is returned:

     shop  | 'A' | 'B' 
-------+-----+-----
 shopC |     | 212
 shopB |  68 |    
 shopA |     | 161
(3 rows)