Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

psql-like \crosstabview feature #1378

Open
Smylers opened this issue Nov 4, 2022 · 0 comments
Open

psql-like \crosstabview feature #1378

Smylers opened this issue Nov 4, 2022 · 0 comments

Comments

@Smylers
Copy link

Smylers commented Nov 4, 2022

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants