Re: Question(s) about crosstab

Hi,

Once I faced the same problem of adding new type and reqriting the query
working with crosstab function. Then I created a dynamic crosstab function.
You may have a look at it if it work out for you:
http://www.rummandba.com/2013/03/postgresql-dynamic-crosstab-function.html

Thanks.

On Tue, Dec 17, 2013 at 3:31 PM, Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> wrote:

> 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:
>
> \d unit_hold
> Table "public.unit_hold"
> Column | Type | Modifiers
> ----------------------+-----------------------+-----------
> grant_number_code | character varying(10) |
> housing_project_code | character varying(10) |
> unit_type_code | character varying(10) |
> count | bigint |
>
> SELECT * FROM unit_hold limit 3;
> grant_number_code | housing_project_code | unit_type_code | count
> -------------------+----------------------+----------------+-------
> 1 | AAAA | 4BR | 1
> 1 | BBBB | 1BR | 1
> 1 | CCCC | 1BR | 1
>
> SELECT unit_type_code,description FROM l_unit_type;
> unit_type_code | description
> ----------------+-------------
> 5BR | 5 Bedroom
> 4BR | 4 Bedroom
> 3BR | 3 Bedroom
> 6BR | 6 Bedroom
> UNKNOWN | Unknown
> GROUP | Group Home
> 2BR | 2 Bedroom
> 1BR | 1 Bedroom
> 0BR | Studio
> SRO | SRO
>
>
> 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?
>
> Thanks in advance!
>
> Ken
>
>
> --
> AGENCY Software
> A data system that puts you in control
> 100% Free Software
> *http://agency-software.org/ <http://agency-software.org/>*
> ken(dot)tanzer(at)agency-software(dot)org<https://mail.google.com/mail/?view=cm&fs=1&tf=1&to=ken(dot)tanzer(at)agency-software(dot)org>
> (253) 245-3801
>
> Subscribe to the mailing list<https://mail.google.com/mail/?view=cm&fs=1&tf=1&to=agency-general-request(at)lists(dot)sourceforge(dot)net&body=subscribe>
> to
> learn more about AGENCY or
> follow the discussion.
>