Re: Question(s) about crosstab

Regarding crosstab, yes it's basically too complicated to use directly. Here are the options:

1) write code (in another language, perhaps) to create your cross tab queries by
inspecting the tables, which then submits those queries to create views. We have a web-app in
django/python that will create crosstab views in this way. (We use it to attach the values to spatial shapes
in PostGIS, so that other GIS programs, such as mapserver, can use it. GIS programs always
seem to expect things to be in crosstab format.)

2) Export to CSV, then pivottable in Excel, or AWK, or Perl, or whatever. The problem with this
is that the results are NOT in your database, they are in the external table.

3) Can't "someone" write a pl language routine that does it better? I'd be willing to work on the core
functionality in python if someone else would be willing to embed it in plpython (I've never used plpython.)

> ----------------------------------------------------------------------
>
> Date: Tue, 17 Dec 2013 15:31:54 -0800
> From: Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>
> Hi. I've got a simple table unit_hold, with grant numbers, buildings and
> counts of unit types, which I need to summarize, along with a table listing
> unit types:

<snip>
>
> I thought this would be a good candidate for crosstab. After wrestling
> with the documentation, this is the best I could come up with:
>
> SELECT * FROM crosstab(
> 'SELECT housing_project_code||''_''||grant_number_code AS
> project_and_grant,grant_number_code,housing_project_code,unit_type_code,count
> FROM unit_hold ORDER BY 1,2',
> 'SELECT * FROM (SELECT DISTINCT unit_type_code FROM l_unit_type) foo
> ORDER BY unit_type_code ~ ''^[0-9]'' DESC, unit_type_code'
> ) AS ct(project_and_grant varchar, grant_number_code varchar,
> housing_project_code varchar, "0BR" bigint, "1BR" bigint, "2BR" bigint,
> "3BR" bigint, "4BR" bigint, "5BR" bigint, "6BR" bigint,"GROUP" bigint,
> "SRO" bigint, "UNKNOWN" bigint)
>
> So here are my questions:
>
> 1) Is there a simpler way? I'm hoping I made this unnecessarily
> cumbersome and complicated.
> 2) AFAICT, if a new unit type were to be added, I'd have to rewrite this
> query. Is there any way to avoid that?
> 3) It seems like everything after the first query, except for the category
> field, is redundant information, and that in theory you should be able to
> say crosstab('query','category_field'). Is there any inherent reason this
> simpler form couldn't work, or is it just that no one has wanted to do it,
> or gotten to it yet?
>

And from David Johnston:

> 1) Pivot Tables...(not a PostgreSQL feature I'm afraid)
>
> 2) Not that I am aware of. I would suggest writing the query so that
> "Other" is a valid group and any unmapped types get aliased to "Other" so at
> least the query counts everything and you know that if "Other" is non-zero
> you have some alterations to make.
>
> 3) Limitation of SQL - explained below:
>
>
>
>
And from Scott Marlowe:

> In the past I've written simple bash, perl, php etc scripts that
> interrogated catalogs and then built my crosstab queries for me. You
> could do it in a pl language, tho probably not easily in plpgsql.
> plpython or plperl etc would proabably be a good place to start.
>