Hi 2016-01-22 19:53 GMT+01:00 Daniel Verite <dan...@manitou-mail.org>:
> Hi, > > Here's an updated patch improving on how the horizontal and vertical > headers can be sorted. > > The discussion upthread went into how it was desirable > to have independant sorts for these headers, possibly driven > by another column, in addition to the query's ORDER BY. > > Thus the options now accepted are: > > \crosstabview [ [-|+]colV[:scolV] [-|+]colH[:scolH] [colG1[,colG2...]] ] > > The optional scolV/scolH columns drive sorts for respectively > colV/colH (colV:scolV somehow means SELECT colV from... order by scolV) > > colG1,... in 3rd arg indicate the columns whose contents form the grid > cells, the typical use case being that there's only one such column. > By default it's all columns minus colV and colH. > > For example, > > SELECT > cust_id, > cust_name, > cust_date, > date_part('month, sales_date), > to_char(sales_date, 'Mon') as month, > amount > FROM sales_view > WHERE [predicates] > [ORDER BY ...] > > If we want to look at <amount> in a grid with months names across, sorted > by month number, and customer name in the vertical header, sorted by date > of > acquisition, we could do this: > > \crosstabview +cust_name:cust_date +5:4 amount > > or letting the vertical header being sorted by the query's ORDER BY, > and the horizontal header same as above: > > \crosstabview cust_name +5:4 amount > > or sorting vertically by name, if it happens that the ORDER BY is missing > or > is on something else: > > \crosstabview +cust_name +5:4 amount > I am playing with this patch, and I have following comments: 1. maybe we can decrease name to shorter "crossview" ?? I am happy with crosstabview too, just crossview is correct too, and shorter 2. Columns used for ordering should not be displayed by default. I can live with current behave, but hiding ordering columns is much more practical for me 3. This code is longer, so some regress tests are recommended - attached simple test case Regards Pavel > > Best regards, > -- > Daniel Vérité > PostgreSQL-powered mailer: http://www.manitou-mail.org > Twitter: @DanielVerite >
regresstest.sql
Description: application/sql
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers