4 ms·

Just let me write SQL please

by klysm 3y ago

DanAtC 3y ago
Dynamically pivoting SQL is a pain in most dialects
- breadwinner 3y ago
  You can write a normal SELECT * FROM FOO query and use a tool like Pebble Reports to do the pivoting: http://www.pebblereports.com/videos/crosstab/ http://www.pebblereports.com/videos/crosstab/
ChrisClark 3y ago
You can in Google Sheets. Well, close enough at least. Use the QUERY function.
whalesalad 3y ago
duckdb will let you do both! https://duckdb.org/docs/sql/statements/pivot.html https://duckdb.org/docs/sql/statements/pivot.html
yunohn 3y ago
IME pivoting on SQL results is a very common usecase too.
chasd00 3y ago
ignorance is bliss but you should learn to work all your tools well not just rely on one.
baq 3y ago
common use case: get a metric truckload of data from a column store binned per minute (or 5), give the giant sheet to users, users use pivot tables to create hourly/daily/weekly reports as they see fit. (not all users are dumb it turns out)
jabroni_salad 3y ago
Well, another nice thing with pivot tables, in excel at least, is I can just follow the data connection to its home and do whatever I want with that. RBQL in rainbowcsv is my usual.
airstrike 3y ago
In Excel, you can have the source data for the pivot table be an SQL query -- then the pivoting is just for presentation purposes when you get down to the very last layer of groupby's, so to speak You can also use SQL queries as sources for regular tables in Excel (i.e. no pivoting) One of my first roles out of undergrad was to take reports that would take an analyst literally days or weeks to generate into a set of SQL queries that got them very close to the answer with pivot tables (or just tables). Their job went from working on reports to changing a couple parameters in the queries like "current month" or "forecast version", which only took a couple minutes, leaving them with plenty of time to think about new reports to generate, how else to improve current reports, etc. Still one of the most personally satisfying things I've ever done. Granted we can't expect all business users to learn to write SQL (spoilers: they never will). I believe we collectively need a more robust solution than having an SQL angel come by and write queries for business users... it feels obvious and within reach, but no one has really done it yet.
roywiggins 3y ago
If only generating pivot tables in SQL were uniformly easy. Postgres's "crosstab" is awkward at best, forcing you to specify column names every time makes exploration unpleasant. In the end I found it easier to do crosstabulation in the UI (or at least, not in SQL).

Just let me write SQL please

by klysm 3y ago

Just let me write SQL please

DanAtC 3y ago

Dynamically pivoting SQL is a pain in most dialects

breadwinner 3y ago
You can write a normal SELECT * FROM FOO query and use a tool like Pebble Reports to do the pivoting: http://www.pebblereports.com/videos/crosstab/ http://www.pebblereports.com/videos/crosstab/

ChrisClark 3y ago

You can in Google Sheets. Well, close enough at least. Use the QUERY function.

whalesalad 3y ago

duckdb will let you do both! https://duckdb.org/docs/sql/statements/pivot.html https://duckdb.org/docs/sql/statements/pivot.html

yunohn 3y ago

IME pivoting on SQL results is a very common usecase too.

chasd00 3y ago

ignorance is bliss but you should learn to work all your tools well not just rely on one.

baq 3y ago

common use case: get a metric truckload of data from a column store binned per minute (or 5), give the giant sheet to users, users use pivot tables to create hourly/daily/weekly reports as they see fit. (not all users are dumb it turns out)

jabroni_salad 3y ago

Well, another nice thing with pivot tables, in excel at least, is I can just follow the data connection to its home and do whatever I want with that. RBQL in rainbowcsv is my usual.

airstrike 3y ago

In Excel, you can have the source data for the pivot table be an SQL query -- then the pivoting is just for presentation purposes when you get down to the very last layer of groupby's, so to speak You can also use SQL queries as sources for regular tables in Excel (i.e. no pivoting) One of my first roles out of undergrad was to take reports that would take an analyst literally days or weeks to generate into a set of SQL queries that got them very close to the answer with pivot tables (or just tables). Their job went from working on reports to changing a couple parameters in the queries like "current month" or "forecast version", which only took a couple minutes, leaving them with plenty of time to think about new reports to generate, how else to improve current reports, etc. Still one of the most personally satisfying things I've ever done. Granted we can't expect all business users to learn to write SQL (spoilers: they never will). I believe we collectively need a more robust solution than having an SQL angel come by and write queries for business users... it feels obvious and within reach, but no one has really done it yet.

roywiggins 3y ago

If only generating pivot tables in SQL were uniformly easy. Postgres's "crosstab" is awkward at best, forcing you to specify column names every time makes exploration unpleasant. In the end I found it easier to do crosstabulation in the UI (or at least, not in SQL).