Question(s) about crosstab

Поиск

Список

Период

Сортировка

От	Ken Tanzer
Тема	Question(s) about crosstab
Дата	18 декабря 2013 г. 02:32:42
Msg-id	CAD3a31WfKn_BL4FHxGT9YtxwNf7FxmmbR4xGoS5hbKtnZzG18w@mail.gmail.com обсуждение исходный текст
Ответы	Re: Question(s) about crosstab Re: Question(s) about crosstab Re: Question(s) about crosstab Re: Question(s) about crosstab
Список	pgsql-general

Дерево обсуждения

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