Re: Question(s) about crosstab

O
n Tue, 2013-12-17 at 15:31 -0800, Ken Tanzer wrote:

>
> \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/
> ken(dot)tanzer(at)agency-software(dot)org
> (253) 245-3801
>
>
> Subscribe to the mailing list to
> learn more about AGENCY or
> follow the discussion.

SELECT UH.grant_number_code, UH.housing_project_code, UH. count,
UT.description
FROM l_unit_type UT, unit_hold UH
WHERE UH.unit_type_code = UT.unit_type_code;

Easier to create a view.