Question(s) about crosstab

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.