Hi,
I want to suggest a client-side \pivot command in psql, implemented
in the attached patch.
\pivot takes the current query in the buffer, execute it and
display it pivoted by interpreting the result as:
column1 => row in pivoted output
column2 => column in pivoted output
column3 => value at (row,column) in pivoted ouput
The advantage over the server-side crosstab() from contrib is in
ease of use, mostly because \pivot doesn't need in advance the
list of columns that result from pivoting.
Typical use cases are queries that produce values
along two axes:
SELECT a,b,aggr(something) FROM tbl GROUP a,b;
\pivot displays immediately the matrix-like representation
Or displaying "pairing" between columns, as in
SELECT a,b,'X' FROM tblA [LEFT|RIGHT|FULL] JOIN tblB...
which once pivoted shows in an easily readable way
what "a" is/isn't in relation with any "b".
Columns are sorted with strcmp(). I think a more adequate sort could
be obtained through a separate query with ORDER BY just for these
values (casted to their original type), but the patch doesn't do that
yet.
Also, \pivot could take optionally the query as an argument instead
of getting it only from the query buffer.
Anyway, does it look useful enough to be part of psql?
I guess I should push this to commitfest if that's the case.
Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite