T-SQL Pivot and UnPivot
Pivot and Unpivot in Transact SQL are the relational operators. They transform one table into another to achieve a clear view of the table.
The Pivot operator converts the row data into a column data.
The Unpivot relational operator works the opposite of the Pivot operator. It converts the column-based data into row-based data and row-based data into a column based data.
Syntax
1. Pivot:
2. UnPivot:
Example1-
Here, we are creating a table name is “tutoraspire,” and values are Course name, Course category, Price, and Values.
The output we get is:
| COURSE NAME | COURSE CATEGORY | PRICE |
|---|---|---|
| C | PROGRAMMING | 5000 |
| JAVA | PROGRAMMING | 6000 |
| PYTHON | PROGRAMMING | 8000 |
| PLACEMENT 100 | INTERVIEW PREPARATION | 5000 |
Now, applying the PIVOT operator to this data:
After using the Pivot operator, we get the following result:
| COURSE NAME | PROGRAMMING | INTERVIEW PREPARATION |
|---|---|---|
| C | 5000 | NULL |
| JAVA | 6000 | NULL |
| PLACEMENT 100 | NULL | 5000 |
| PYTHON | 8000 | NULL |
Example 2-
Now, we are using the same table “tutoraspire” created in the above example and applying the Unpivot operator to our Pivoted table.
Applying UNPIVOT operator below:
After using the Unpivot operator, we got our original table back as we have successfully transformed the columns of the table back to the rows:
| COURSE NAME | COURSE CATEGORY | PRICE |
|---|---|---|
| C | PROGRAMMING | 5000 |
| JAVA | PROGRAMMING | 6000 |
| PLACEMENT100 | INTERVIEW PREPARATION | 5000 |
| PYTHON | PROGRAMMING | 8000 |