CODEWITHSUNDEEP
snowflake-cloud-data-platform
Zoom
Zoom

Reputation: 75

Pivot snowflake columns to rows

I have table as below:

Just FYI i have over 200 columns in Key column i needed pivoted as final table.

key value
column1 abc
column2 xyz
column3 123

The final table i would like to pivot is:

column1 column2 column3
abc xyz 123

Original file came in avro format and it was loaded in snowflake as first table.

Upvotes: 0

Views: 54

Answers (1)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1606

Hi you can try using Pivot, if you need to dynamically have the column names then you can use this link which has an example by Felipe Hoffa. https://medium.com/snowflake/dynamic-pivots-in-sql-with-snowflake-c763933987c

with data1 as 
(select 'column1' as key1 , 'abc' value1
union all select 'column2' as key1 , 'xyz'
union all select 'column3' as key1 , '123'
)
SELECT * from data1
pivot(max(key1 )for value1 in ('abc','xyz','123')) as p;

 PIVOT(SUM(amount) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR'))
      AS p
  ORDER BY EMPID;

Upvotes: 0

Related Questions