How to UNPIVOT data in Snowflake

When working with data in Snowflake, you might encounter situations where you need to transform columns into rows. This process is known as "unpivoting." Let's walk through an example to illustrate how to unpivot data in Snowflake.

Step 1: Create the Table

Suppose you have the following table structure:

CREATE TABLE DEMO_Students (
    Name VARCHAR(50),
    Surname VARCHAR(50),
    Period INT,
    Sport INT,
    History INT,
    English INT,
    Geography INT
);

Step 2: Insert Sample Data

Next, we'll populate the table with some sample data:

INSERT INTO
DEMO_Students(Name, Surname, Period, Sport, History, English, Geography)
VALUES
('Luke', 'Green', 1, 30, 20, 23, NULL),
('Mary', 'Brown', 1, 17, 15, NULL, 30),
('John', 'Red', 1, 18, NULL, 21, 30),
('Walter', 'White', 1, 22, 20, 5, 30),
('Luke', 'Green', 2, 30, 20, 23, NULL),
('Mary', 'Brown', 2, NULL, 15, 17, 30),
('John', 'Red', 2, 18, NULL, 11, 30),
('Walter', 'White', 2, 2, 32, 1, 30),
('Luke', 'Green', 3, 20, 15, 15, 12),
('Mary', 'Brown', 3, 0, 3, NULL, 4),
('John', 'Red', 3, 18, 40, 21, 30),
('Walter', 'White', 3, 17, 19, 15, 30),
('John', 'Red', 3, NULL, 23, 23, 30);

Step 3: Preview the Original Table

Here's a preview of the original table:

SELECT *
DEMO_Students;

Step 4: Unpivot the Data

To unpivot the data and transform the subject columns into rows, use the following SQL snippet:

SELECT 
    Surname, 
    Name, 
    Subject, 
    Grade
FROM 
    DEMO_Students
UNPIVOT (
    Grade FOR Subject IN (Sport, History, English, Geography)
) AS Unpvt
ORDER BY 
    Surname, 
    Name, 
    Period;

This query uses the UNPIVOT operator to convert the columns Sport, History, English, and Geography into rows under the Subject column, with the corresponding grades in the Grade column. The result is a more normalized view of the student grades data, making it easier to analyze individual subjects.