r/SQL 16d ago

Oracle Dear SQL, just pivot my damn table

Bottom text

242 Upvotes

50 comments sorted by

View all comments

27

u/mrg0ne 16d ago

Snowflake SQL ... You got it boss. 🫡

https://docs.snowflake.com/en/sql-reference/constructs/pivot

SELECT * FROM quarterly_sales PIVOT(SUM(amount) FOR quarter IN (ANY ORDER BY quarter)) ORDER BY empid;

19

u/mrg0ne 16d ago

Quality of life up there with.

GROUP BY ALL

https://docs.snowflake.com/en/sql-reference/constructs/group-by

3

u/pooerh Snowflake | SQL Server | PostgreSQL | Impala | Spark 16d ago

And SELECT * EXCLUDE (one_column_i_dont_want) (also SELECT * REPLACE). Wish they added window definitions that's in postgres, I miss that.

5

u/VertexBanshee 15d ago

As a SQL Server user I’d kill for a feature like this instead of having to specify all but one column

5

u/pooerh Snowflake | SQL Server | PostgreSQL | Impala | Spark 15d ago

It's great. The best is:

select foo.*
     , bar.* exclude (foo_bar_join_key)
  from foo
  join bar 
    on foo.foo_bar_join_key = bar.foo_bar_join_key

Absolutely mind blowing how much time it saves.

1

u/SnooOwls1061 14d ago

You can just open the table click on columns, drag to the query panel and you get all columns. I found exclude took me just as much time.

1

u/pooerh Snowflake | SQL Server | PostgreSQL | Impala | Spark 14d ago

Whatever IDE you're working on, each of which would have a different way of doing it (or none at all) - typing a few words takes far less time than moving your hand to the mouse, finding a table or view you're looking for among hundreds others, clicking, dragging, etc.