postgresql - 如何在PostgreSQL中将行展平为列

使用postgresql 9.3，我有一个表，显示了一年内颁发的独立许可证，如下所示：

permit_typ| zipcode| address| name
-------------+------+------+-----
CONSTRUCTION      | 20004 | 124 fake streeet | billy joe
SUPPLEMENTAL      | 20005 | 124 fake streeet  | james oswald
POST CARD         | 20005 | 124 fake streeet  | who cares
HOME OCCUPATION   | 20007 | 124 fake streeet  | who cares
SHOP DRAWING      | 20009 | 124 fake streeet  | who cares

我想把它弄平所以看起来像

CONSTRUCTION | SUPPLEMENTAL | POST CARD| HOME OCCUPATION | SHOP DRAWING | zipcode
-------------+--------------+-----------+----------------+--------------+--------
1            |  2           | 3         |   5            |   6          |  20004
1            |  2           | 3         |   5            |   6          |  20005
1            |  2           | 3         |   5            |   6          |  20006
1            |  2           | 3         |   5            |   6          |  20007
1            |  2           | 3         |   5            |   6          |  20008

一直在尝试使用Crosstab，但这有点超出了我生疏的SQL经验。任何人都有任何想法

最佳答案

我通常使用条件聚合来处理此类查询。在Postgres中，您可以执行以下操作：

select zipcode,
       sum( (permit_typ = 'CONSTRUCTION')::int) as Construction,
       sum( (permit_typ = 'SUPPLEMENTAL')::int) as SUPPLEMENTAL,
       . . .
from t
group by zipcode;