Re: Question(s) about crosstab - Mailing list pgsql-general

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.tanzer@agency-software.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.