PIVOT

PolarDB PostgreSQL版(兼容Oracle)支持PIVOT语法,您可以将任何查询以交叉表格形式显示。与一般查询相比,交叉表格形式具有更多的列和较少的行。

语法

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

示例

创建示例数据库。

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);
  • 普通形式显示的查询:

    select * from SalesOrder pivot(sum(sales) for product in ('A', 'B'));

    查询结果如下:

     shop  | 'A' | 'B' 
    -------+-----+-----
     shopC |     | 218
     shopB |  68 |    
     shopA |  10 | 161
    (3 rows)
  • 交叉表格形式显示的查询:

    select *
    from (select shop, product, sales
          from SalesOrder
          where sales>10)
    pivot(sum(sales) for product in ('A', 'B'));

    查询结果如下:

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