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

Gordon Linoff · Accepted Answer · 2016-12-18 15:30:53Z

3

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;

answered Dec 18, 2016 at 15:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to flatten rows to columns in postgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related