2015-12-14 23:09 GMT+01:00 Daniel Verite <dan...@manitou-mail.org>: > Pavel Stehule wrote: > > > postgres=# \crosstabview 4 +month label > > > > Maybe using optional int order column instead label is better - then you > can > > do sort on client side > > > > so the syntax can be "\crosstabview VCol [+/-]HCol [[+-]HOrderCol] > > In the meantime I've followed a different idea: allowing the > vertical header to be sorted too, still server-side. > > That's because to me, the first impulse for a user noticing that > it's not sorted vertically would be to write > \crosstabview +customer month > rather than figure out the > \crosstabview customer +month_number month_name > invocation. > But both ways aren't even mutually exclusive. We could support > \crosstabview [+|-]colV[:labelV] [+|-]colH[:labelH] > it's more complicated to understand, but not harder to implement. >
yes, I was able to do what I would - although the query was little bit strange select amount, label, customer from (select sum(amount) as amount, extract(month from closed)::int - 1 as Month, to_char(date_trunc('month', closed), 'TMmon') as label, customer from data group by customer, to_char(date_trunc('month', closed), 'TMmon'), extract(month from closed) union select sum(amount), extract(month from closed)::int - 1 as month, to_char(date_trunc('month', closed), 'TMmon') as label, '**** TOTAL ****' from data group by to_char(date_trunc('month', closed), 'TMmon'), extract(month from closed)::int - 1 order by month) x \crosstabview +3 2 > > Also, a non-zero FETCH_COUNT is supported by this version of the patch, > if the first internal FETCH retrieves less than FETCH_COUNT rows. > Otherwise a specific error is emitted. > good idea > > Also there are minor changes in arguments and callers following > recent code changes for \o > > Trying to crosstab with 10k+ distinct values vertically, I've noticed > that the current code is too slow, spending too much time > sorting. I'm currently replacing its simple arrays of distinct values > with AVL binary trees, which I expect to be much more efficient for > this. > Regards Pavel > > Best regards, > -- > Daniel Vérité > PostgreSQL-powered mailer: http://www.manitou-mail.org > Twitter: @DanielVerite >