I vaguely understand (on a theoretical level) why the syntax for pivoting in databases is necessarily convoluted, but it does underscore to me how spreadsheets have a great advantage for everyday data-exploring, at least when it comes to ease of pivoting.<p>(by "everyday", I mean working with datasets small enough to fit within a spreadsheet's limits, and aren't yet important enough to need the reliability/regularity that comes with importing it to a database)
I had a quick look at some online material on implementations of pivot, as well as what I consider my go-to reference [0] for Apache projects' SQL support (Apache Calcite).<p>Looks like each DB has a proprietary way of pivoting, and I couldn't find anything on pivot in Calcite. Does anyone know if the SQL impl of pivot was created specifically for Spark, or whether it'd be upstreamed to Calcite? The benefit would be that other projects that rely on Calcite for SQL support would also have pivot capabilities. I'm thinking of Apache Beam.<p>- [0] <a href="https://calcite.apache.org/docs/reference.html" rel="nofollow">https://calcite.apache.org/docs/reference.html</a>
This is very nice. I've currently had to resort to MariaDB's dynamic columns for my purposes (much nicer than a traditional entity-attribute-value solution) but this looks even nicer. Is this a Spark-only thing?
mySQL needs this as well. We do a lot of pivot style reports for clients and while we have it down to a bit of a science now the resulting SQL is a debugging mess and not terribly efficient. (though it still runs fairly quick)
Off topic: the GPDR banner on this site is a good example of dark pattern. Scrolling into a webpage does not mean I consent collecting personal info about me.