Snowflake - How to pivot columns in database

by Liu Zhang
This is HIVERY trademark tagline. Data Has A Better Idea. Visit www.hivery.com to learn more about who we are.
Photo by Franki Chamaki / Unsplash

In the final results of the previous blog, we encountered an issue with the parsed data, where the inner array returned data in the following format.

Initial result columns

As all values are stored in the same way in JSON, regardless of the type, e.g. numerical, string, boolean etc. The resulting table structure is not desired as we want only same measure to be within the same column/field.

To get to the correct format, we need to perform a pivot on the table from long to wide format. This is a common feature in data cleaning tool, but not a standard basic SQL query call.

Syntax:

SELECT ...

FROM ...  

PIVOT ( <aggregate_function> ( <pivot_column> )            

FOR <value_column> IN ( <pivot_value_1> [ , <pivot_value_2> ... ] ) ) [ ... ]

Pivot is a function after the From statement, where we need to select a pivot column with an aggregation method and a value column.

Sample code

We enclose the first part of parsed table in one, where we call it t2, then wrap pivot function in a bracket. Follow the syntax it is easy to see how it can be applied.

Note:

1: Max is used as aggregation method since there is only one column for value. Concat doesn't apply.

2: Column names need to be renamed in order for all columns selected, not just pivoted columns.

Results - 782 rows

We see the results is back to 728 rows as the 9 objects for each ID has pivoted to become 9 columns.


Looking for more guides, tips and tricks in Tableau or Alteryx? Go check out the other blog posts from the Data School.

Tableau Public

LinkedIn

Twitter

Tue 14 Dec 2021

Sun 03 Oct 2021

Tue 28 Sep 2021