On Tue, Dec 17, 2013 at 3:31 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:
> 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
intheory you should be able to say crosstab('query','category_field'). Is there any inherent reason this simpler form
couldn'twork, or is it just that no one has wanted to do it, or gotten to it yet?
Try to look at this article [1]. The guy has made some plpgsql
automation so it generate the resulting crostab query kind of like you
described it in 3, and it looks like is solves 1 and 2. For complex
queries you can make views and use them with the tablename argument.
[1] http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/
--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA
http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com