how to flatten rows to columns in postgreSQL

Question

using postgresql 9.3 I have a table that shows indivual permits issued across a single year below:

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

I am trying to flatten this so it looks like

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

have been trying to use Crosstab but its a bit above my rusty SQL experiance. anybody have any ideas

Answer 1

I usually approach this type of query using conditional aggregation. In Postgres, you can do:

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