We'll definitely add a PRQL/SQL block to the UI soon (you will be able to toggle to select which one you'd like).
On that point - PRQL doesn't natively support PIVOT statements. As a hack, we made our own flavor of PRQL with a PIVOT statement and we parse that to SQL CTEs to make it work for now. I will be submitting a pull request to PRQL to add support to get around this.
We're working on saving queries to local storage as well as sharing via downloadable config files (we're thinking a PRQL file with some sugar that when uploaded, recreates the entire transform)
I'll take a look and if it's simply a matter of changing from the PRQL compiler to EdgeQL compiler, then, we should be able to have land in main in short order. It's not the highest priority thing right now though.
Not ideal because it’s just wasting credits, but it worked for me.
Love what you've done and thanks for building on PRQL!
We're very happy to add something like a PIVOT statement. In the long term, we'd have to think about how to make it work deeply in PRQL, since the column names are suddenly runtime dependent. In the short term, we should definitely make PRQL work for your case — it's important that there's an escape hatch for things that aren't natively supported by PRQL yet. (We have s-strings but I'm guessing they don't cover this specific case?)
That makes sense. I ran into some old issues about Pivot but it seems it's not been implemented yet. And yes, s-strings wouldn't work - the DuckDB pivot statement looks like this:
PIVOT ⟨dataset⟩
ON ⟨columns⟩
USING ⟨values⟩
GROUP BY ⟨rows⟩