How to PIVOT Data in Snowflake

Step 1: Create the Table

To start, create a table with the following structure:

CREATE TABLE Sample_Student_Grades (
    Surname VARCHAR(50),
    Name VARCHAR(50),
    Subject VARCHAR(50),
    Grade INT
);

Step 2: Insert Sample Data

Next, populate the table with some sample data:

INSERT INTO Sample_Student_Grades (Surname, Name, Subject, Grade) VALUES
    ('Brown', 'Mary', 'Sport', 17),
    ('Brown', 'Mary', 'History', 15),
    ('Brown', 'Mary', 'English', NULL),
    ('Brown', 'Mary', 'Geography', 30),
    ('Green', 'Luke', 'Sport', 30),
    ('Green', 'Luke', 'History', 20),
    ('Green', 'Luke', 'English', 23),
    ('Green', 'Luke', 'Geography', NULL),
    ('Red', 'John', 'Sport', 18),
    ('Red', 'John', 'History', NULL),
    ('Red', 'John', 'English', 21),
    ('Red', 'John', 'Geography', 30),
    ('White', 'Walter', 'Sport', 22),
    ('White', 'Walter', 'History', 20),
    ('White', 'Walter', 'English', 5),
    ('White', 'Walter', 'Geography', 30),
    ('Brown', 'Alice', 'Sport', 16),
    ('Brown', 'Alice', 'History', 18),
    ('Brown', 'Alice', 'English', NULL),
    ('Brown', 'Alice', 'Geography', 25),
    ('Green', 'Anna', 'Sport', 29),
    ('Green', 'Anna', 'History', 24),
    ('Green', 'Anna', 'English', 19),
    ('Green', 'Anna', 'Geography', NULL),
    ('Black', 'Tom', 'Sport', 12),
    ('Black', 'Tom', 'History', 20),
    ('Black', 'Tom', 'English', NULL),
    ('Black', 'Tom', 'Geography', 15),
    ('Blue', 'Laura', 'Sport', 15),
    ('Blue', 'Laura', 'History', 22),
    ('Blue', 'Laura', 'English', 27),
    ('Blue', 'Laura', 'Geography', 30),
    ('Yellow', 'Mike', 'Sport', 19),
    ('Yellow', 'Mike', 'History', NULL),
    ('Yellow', 'Mike', 'English', 25),
    ('Yellow', 'Mike', 'Geography', 28);

Step 3: Preview the Original Table

Here's a preview of the original table:

SELECT *
FROM Sample_Student_Grades;

Step 4A: Pivot Table with Fixed (Hard Coded) Columns

To pivot the table with fixed columns, use the following query:

-- Fixed columns
SELECT *
FROM Sample_Student_Grades
PIVOT
    (MAX(Grade) 
     FOR Subject IN ('Sport', 'History', 'English')
    ) AS PivotTable
ORDER BY 
    Surname, 
    Name;

Step 4B: Pivot Table with Dynamic Columns

For a more flexible approach, you can pivot the table with dynamic columns. This query adjusts based on the available subjects in the table:

-- Dynamic columns
SELECT *
FROM Sample_Student_Grades
PIVOT
    (MAX(Grade) 
     FOR Subject IN (SELECT DISTINCT Subject FROM Sample_Student_Grades ORDER BY Subject)
    ) AS PivotTable
ORDER BY 
    Surname, 
    Name;

In this query, the subquery (SELECT DISTINCT Subject FROM Sample_Student_Grades ORDER BY Subject) dynamically retrieves the distinct subjects to use as pivot columns. This method ensures that the pivot operation adapts to any changes in the subject data.