3

So I can't figure out how to get tables to pivot in Snowflake. I'm trying to model the help page here https://docs.snowflake.com/en/sql-reference/constructs/pivot.html.

In this example, I'm only pulling 3 columns, the step, the parameter being measured and the value. Trying to pivot it so each parameter is in its own column.

source output

step_name    Parameter_Name Value
----------------------------------
A            Item1          75
A            Item2          32
B            Item1          45
B            Item2          62

pivot output

step_name    Item1    Item2
--------------------------
 A            75       32
 B            45       62

sql text:

select 
    step_name, 
    PARAMETER_NAME,
    Value

from "METRO_TABLE" 
pivot (avg(Value) for PARAMETER_NAME in ('Item1', 'Item2'))
as p

WHERE 
and PARAMETER_NAME in ('Item1','Item2')

limit 50
GMB
  • 216,147
  • 25
  • 84
  • 135
surfer349
  • 107
  • 3
  • 10
  • I wrote a Snowflake stored procedure to get dynamics pivots inside Snowflake, check https://hoffa.medium.com/dynamic-pivots-in-sql-with-snowflake-c763933987c – Felipe Hoffa Feb 08 '21 at 21:38

3 Answers3

4

Just use conditional aggregation. The syntax works across most databases, and does not require remembering the slight variations of each vendor-specific implementation. It is also more flexible (although this does not make a difference for this simple use-case):

select
    step_name,
    max(case when parameter_name = 'Item1' then value end) Item1,
    max(case when parameter_name = 'Item2' then value end) Item2
from metro_table
where parameter_name in ('Item1', 'Item2')
group by step_name

In absence of an order by clause, I removed the limit clause from your query: if you want a stable subsets of rows, then use both order by and limit.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • awesome! that works on this. So next question, adding in more columns seems to throw an error. How would I use this back into a bigger query? For example, pulling Lot_ID columns, other identifying columns? SQL compilation error: error line 2 at position 4 'metro_table.Lot_ID' in select clause is neither an aggregate nor in the group by clause. – surfer349 Aug 26 '20 at 00:03
  • 1
    @surfer349: comments are not really meant to ask new questions - instead, you can [ask a new question](https://stackoverflow.com/questions/ask), providing proper sample data and desired results. Maybe you just want to add those columns in both the `select` *and* `group by` clauses? – GMB Aug 26 '20 at 00:26
  • as simple as that! thanks @GMB – Juned Ansari Dec 27 '21 at 13:56
  • @surfer349 don't forget to mark the question as answered, if it has been – Cassandra S. Jun 30 '22 at 06:08
2

Using Snowflake syntax, the following SQL gives the output.

 Select *
  FROM METRO_TABLE
  PIVOT(sum(value) for parameter_name in ('Item1','Item2'))
  AS P (Step_Name,Item_1,Item_2)
  ORDER BY step_name;
Monem_منعم
  • 336
  • 2
  • 10
1

I had a similar issue. I had to use a CTE to get it to work properly.

WITH metro_tbl AS (

  select 
      step_name, 
      parameter_name,
      value
  from metro_table
  where parameter_name in ('Item1','Item2')
  limit 50 
)
 
  select * 
  from metro_tbl
  pivot(avg(value) for parameter_name in ('Item1', 'Item2'))
Jacob B
  • 11
  • 2