5 ms·

Is there anyway to support N months? This one has them hardcoded in, but what if I don't know how many months are going to be in the dataset?

MarkusWinand 9y ago
The example still groups by YEAR. So you won't need more than 12 months. Regardless: SQL is a statically typed language. Also the type of the result—i.e. the names and types of the columns—must be known in advance. Some databases offer extensions for "dynamic pivot".
- mwexler 9y ago
  Markus is modest; he is the author of the top post, and imho is up with Joe Celko in offering great techniques to use SQL effectively (both at http://modern-sql.com/ http://modern-sql.com/ as above and http://use-the-index-luke.com/ http://use-the-index-luke.com/). I'm a fan.
thenaturalist 9y ago
Yes, if you take a detour via Python. As Markus pointed out, you would still need to know data types and filter values in advance to configure the pivot - I would assume this is normally the case. Using Python, you can build conditional rules for building a SQL query based on parametrized inputs with Jinja2. Example here: https://multithreaded.stitchfix.com/blog/2017/07/06/one-weird-trick/ https://multithreaded.stitchfix.com/blog/2017/07/06/one-weir... You can then execute said query with a given DB module.
makmanalp 9y ago
In postgres you can use the crosstab function with two SQL queries, the first to get the data to pivot, the second to dynamically query the categories from the data (e.g. some kind of SELECT DISTINCT).
- makmanalp 9y ago
  Actually, I just realized I'm wrong. You can do my trick to automatically filter values, but you still have to pre-declare the schema of the table being returned. This means you can't have a dynamic pivot where maybe you end up with 6 columns instead of 5.
powvans 9y ago
It's possible to do it with a prepared statement. I just did this a couple of weeks ago and it worked nicely. Inspired by this Stack Overflow answer: https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns/12005676#12005676 https://stackoverflow.com/questions/12004603/mysql-pivot-row... I wouldn't recommend this unless it really fits your use case. For me I needed to report whether a set of users had completed various tasks. The tasks wouldn't be known ahead of time, but the report is always limited to a set of users who share the same set of tasks. This approach allowed me to build a report that works for different sets of users and the tasks don't need to be known ahead of time. Big downside is that my query creates a bunch of dependent subqueries. Because the number of rows (users) and tasks (columns) in a given report is always within a reasonable number, it runs quickly. Your mileage may vary.
sreaching 9y ago
dynamic sql is the ugly solution - using a loop to generate the sql string to be executed.
- fishcolorbrick 9y ago
  This is what I use at work. :) It can make the code difficult to read and update later though.
netcraft 9y ago
I mentioned this in another comment, but with postgres you can use jsonb_object_agg and get out a variable number of columns, you just have to pull the data out of the json blob instead of them being columns.

MarkusWinand 9y ago

The example still groups by YEAR. So you won't need more than 12 months. Regardless: SQL is a statically typed language. Also the type of the result—i.e. the names and types of the columns—must be known in advance. Some databases offer extensions for "dynamic pivot".

mwexler 9y ago
Markus is modest; he is the author of the top post, and imho is up with Joe Celko in offering great techniques to use SQL effectively (both at http://modern-sql.com/ http://modern-sql.com/ as above and http://use-the-index-luke.com/ http://use-the-index-luke.com/). I'm a fan.

thenaturalist 9y ago

Yes, if you take a detour via Python. As Markus pointed out, you would still need to know data types and filter values in advance to configure the pivot - I would assume this is normally the case. Using Python, you can build conditional rules for building a SQL query based on parametrized inputs with Jinja2. Example here: https://multithreaded.stitchfix.com/blog/2017/07/06/one-weird-trick/ https://multithreaded.stitchfix.com/blog/2017/07/06/one-weir... You can then execute said query with a given DB module.

makmanalp 9y ago

In postgres you can use the crosstab function with two SQL queries, the first to get the data to pivot, the second to dynamically query the categories from the data (e.g. some kind of SELECT DISTINCT).

makmanalp 9y ago
Actually, I just realized I'm wrong. You can do my trick to automatically filter values, but you still have to pre-declare the schema of the table being returned. This means you can't have a dynamic pivot where maybe you end up with 6 columns instead of 5.

powvans 9y ago

It's possible to do it with a prepared statement. I just did this a couple of weeks ago and it worked nicely. Inspired by this Stack Overflow answer: https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns/12005676#12005676 https://stackoverflow.com/questions/12004603/mysql-pivot-row... I wouldn't recommend this unless it really fits your use case. For me I needed to report whether a set of users had completed various tasks. The tasks wouldn't be known ahead of time, but the report is always limited to a set of users who share the same set of tasks. This approach allowed me to build a report that works for different sets of users and the tasks don't need to be known ahead of time. Big downside is that my query creates a bunch of dependent subqueries. Because the number of rows (users) and tasks (columns) in a given report is always within a reasonable number, it runs quickly. Your mileage may vary.

sreaching 9y ago

dynamic sql is the ugly solution - using a loop to generate the sql string to be executed.

fishcolorbrick 9y ago
This is what I use at work. :) It can make the code difficult to read and update later though.

netcraft 9y ago

I mentioned this in another comment, but with postgres you can use jsonb_object_agg and get out a variable number of columns, you just have to pull the data out of the json blob instead of them being columns.