One of the many powerful features of Snowflake is its UNPIVOT function, which allows users to transform columns into rows in a table. In this article, we will discuss the Snowflake UNPIVOT function with a detailed example using the table name freshers_in.
Introduction to UNPIVOT Function
The UNPIVOT function in Snowflake is used to transform columns in a table into rows. This function is very useful when we want to convert a table with a wide format into a long format. The UNPIVOT function in Snowflake works in the opposite direction of the PIVOT function, which is used to transform rows into columns.
Syntax of UNPIVOT Function
The syntax for the UNPIVOT function in Snowflake is as follows:
SELECT * FROM freshers_in UNPIVOT (
unpivoted_column FOR pivot_column IN (column1, column2, ..., columnN)
);
Here, the UNPIVOT function is called on the table freshers_in. The unpivoted_column is the new column that will be created to store the values from the pivoted columns. The pivot_column is the column or columns that will be transformed into rows. The column1, column2, …, columnN are the names of the columns to be pivoted.
Example of UNPIVOT Function
Suppose we have a table called freshers_in with the following structure:
CREATE TABLE freshers_in (
Name VARCHAR(50),
Maths INT,
Science INT,
English INT
);
This table contains data about the marks scored by students in Maths, Science, and English. The data is stored in a wide format, where each subject is a separate column. However, we want to transform this table into a long format where each row represents a student and a subject. We can use the UNPIVOT function in Snowflake to achieve this.
The below statement adds three rows of data to the freshers_in table, with the name and marks for each subject for each student. You can add more rows to this statement as needed to insert additional data.
INSERT INTO freshers_in (Name, Maths, Science, English)
VALUES ('Sachin', 90, 85, 95),
('Bob', 80, 90, 75),
('Carol', 75, 80, 85);
The following query uses the UNPIVOT function to transform the columns in the freshers_in table into rows:
SELECT Name, pivot_column AS Subject, unpivoted_column AS Marks
FROM freshers_in
UNPIVOT (Marks FOR pivot_column IN (Maths, Science, English));
Here, we have selected the Name column from the freshers_in table and used the UNPIVOT function to transform the Maths, Science, and English columns into rows. The UNPIVOT function creates a new column called Marks to store the values from the pivoted columns, and a column called Subject to store the names of the columns that were pivoted. The result of this query will be a table with the following structure:
| Name | Subject | Marks |
|--------|---------|-------|
| Sachin | Maths | 90 |
| Sachin | Science | 85 |
| Sachin | English | 95 |
| Bob | Maths | 80 |
| Bob | Science | 90 |
| Bob | English | 75 |
| Carol | Maths | 75 |
| Carol | Science | 80 |
| Carol | English | 85 |
This table has been transformed into a long format where each row represents a student and a subject. The Marks column contains the marks scored by the student in the subject, and the Subject column contains the name of the subject.
Snowflake important urls to refer
