SQL Pivot: Converting Rows to Columns

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 &quot;everyday&quot;, I mean working with datasets small enough to fit within a spreadsheet&#x27;s limits, and aren&#x27;t yet important enough to need the reliability&#x2F;regularity that comes with importing it to a database)

I wish there was something like this in postgres. tablefunc is a lot more complicated than this syntax.

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&#x27; SQL support (Apache Calcite).<p>Looks like each DB has a proprietary way of pivoting, and I couldn&#x27;t find anything on pivot in Calcite. Does anyone know if the SQL impl of pivot was created specifically for Spark, or whether it&#x27;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&#x27;m thinking of Apache Beam.<p>- [0] <a href="https:&#x2F;&#x2F;calcite.apache.org&#x2F;docs&#x2F;reference.html" rel="nofollow">https:&#x2F;&#x2F;calcite.apache.org&#x2F;docs&#x2F;reference.html</a>

This is very nice. I&#x27;ve currently had to resort to MariaDB&#x27;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.