psql-like \crosstabview feature

[Smylers]

Description

It'd be nice to be able to view a query result as a 2D table, such as can be done in psql with its \crosstabview command.

For instance suppose you have a stock level table with the stock for each item per location. You can list all the stock levels like this:

SELECT * FROM stock_level ORDER BY item_id, location_id;
 item_id | location_id | stock 
---------+-------------+-------
 IOC     | Harrogate   |     2
 IOC     | Skipton     |     3
 IOS     | Harrogate   |     1
 IOS     | Ilkley      |     6
 KMB     | Ilkley      |     3
 KMB     | Skipton     |     4
 TTR     | Harrogate   |     5
 TTR     | Ilkley      |     2
 TTR     | Skipton     |     1

but it can be more useful to have just one row for each item, using columns for the locations. In psql that involves just running the same query with \crosstabview:

SELECT * FROM stock_level ORDER BY item_id, location_id \crosstabview
 item_id | Harrogate | Skipton | Ilkley 
---------+-----------+---------+--------
 IOC     |         2 |       3 |       
 IOS     |         1 |         |      6
 KMB     |           |       4 |      3
 TTR     |         5 |       1 |      2

Currently I switch from pgcli back to psql every time I want to do this.

(Yes, Postgresql itself has an extension which provides a crosstab() function for construction queries that will do this. But that's much more involved to use, or involves writing out all the column headers (the locations in the above example), or both. psql's command is quick and useful enough to use on ad-hoc queries.)

Your environment

Ubuntu Linux 22.04 LTS pgcli Version: 3.4.1