uwdata
Add support for PIVOT
We have a use case for pivoting data into columns for comparison purposes. The example from the DuckDB docs looks like this
source data
displayed data after pivoting
Where should the PIVOT happen?
Currently, mosaic-sql doesn't support the PIVOT operator. That shouldn't be too hard to add, but I'm curious if you have thoughts about what should be handled by the db/mosaic, and what should be a display side concern. My intuition is that using PIVOT in a query is probably the wrong place to do it, and that we'd be better off keeping it in the format of the source data above, which probably matches other metric displays on the page, and pivoting client side. Thoughts?
I feel this could make sense in the database as it's otherwise a bit of a pain to e.g. support pagination. Having said that, a bunch of our optimizations would have to be carefully reviewed to make sure they still work so I'd say to implement the necessary transformations in JavaScript if you want the feature now.
