Re: Question(s) about crosstab

On Tue, Dec 17, 2013 at 3:31 PM, Ken Tanzer  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 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?

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