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)