Turn values in different rows into column headers with the PIVOT function in Snowflake

,

In last week’s post we talked about our pure, unrelenting hatred for PIVOTED data (you know, when months JAN, FEB, MAR, etc. each have their own column).

As I am trying to hit my quota of hypocrite points this week, I’d like to talk about how to turn UNPIVOTED data into PIVOTED data.  

A lot of database engines have functions for that, such as the PIVOT-function in Snowflake. However, for the sake of keeping up the fundamentals (and doing things the hard way for no reason at all), I also used CASE WHEN to achieve a PIVOTED dataset.

Both methods are shown in the script below.

Now we have a post about PIVOT but also of UNPIVOT. Balance has been brought to the force.

SQL
CREATE OR REPLACE TEMP TABLE TEMP_TOY_SALES(CATEGORY VARCHAR, SALES INT) AS 
-- Original data; unpivoted.
    SELECT * FROM VALUES
        ('cars', 10), 
        ('bikes', 15),
        ('boats', 5)
;

-- Pivot using CASE WHEN
SELECT 
    SUM(CASE WHEN CATEGORY ILIKE 'cars' THEN SALES ELSE 0 END) AS CAR_SALES,
    SUM(CASE WHEN CATEGORY ILIKE 'bikes' THEN SALES ELSE 0 END) AS BIKE_SALES,
    SUM(CASE WHEN CATEGORY ILIKE 'boats' THEN SALES ELSE 0 END) AS BOAT_SALES
FROM TEMP_TOY_SALES
;

-- Pivot using PIVOT function. Column CATEGORY is being "pivoted away"
SELECT 
    *
FROM TEMP_TOY_SALES
    PIVOT(SUM(SALES) FOR CATEGORY IN ('cars', 'bikes', 'boats'))
    AS p (cars, bikes, boats)
;
SQL