Hacker News Reader

Comment by sgarman | Hacker News Reader

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?

View on HN

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".

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/ as above and http://use-the-index-luke.com/). I'm a fan.

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...

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.

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-weir...

You can then execute said query with a given DB module.

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.

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).

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.

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

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

Reply on news.ycombinator.com