In SQL, Pivot and Unpivot are relational operators that are used to transform one table into another in order to achieve more simpler view of table. Conventionally we can say that Pivot operator converts the rows data of the table into the column data. The Unpivot operator does the opposite that is it transform the column based data into rows.
SELECT (ColumnNames) FROM (TableName) PIVOT ( AggregateFunction(ColumnToBeAggregated) FOR PivotColumn IN (PivotColumnValues) ) AS (Alias) //Alias is a temporary name for a table
SELECT (ColumnNames) FROM (TableName) UNPIVOT ( AggregateFunction(ColumnToBeAggregated) FOR PivotColumn IN (PivotColumnValues) ) AS (Alias)
We have created a simple table named “geeksforgeeks” with values like Course name, course category and price and inserted the respective values.
Create Table geeksforgeeks ( CourseName nvarchar(50), CourseCategory nvarchar(50), Price int ) Insert into geeksforgeeks values('C', 'PROGRAMMING', 5000) Insert into geeksforgeeks values('JAVA', 'PROGRAMMING', 6000) Insert into geeksforgeeks values('PYTHON', 'PROGRAMMING', 8000) Insert into geeksforgeeks values('PLACEMENT 100', 'INTERVIEWPREPARATION', 5000) SELECT * FROM geeksforgeeks
The output we get is :
Now, applying PIVOT operator to this data:
SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION FROM geeksforgeeks PIVOT ( SUM(Price) FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION ) ) AS PivotTable
After using Pivot operator we get the following result:
Now, we use the same table “geeksforgeeks” created in the above example and apply the Unpivot operator to our Pivoted table.
Applying UNPIVOT operator:
SELECT CourseName, CourseCategory, Price FROM ( SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION FROM geeksforgeeks PIVOT ( SUM(Price) FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION) ) AS PivotTable ) P UNPIVOT ( Price FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION) ) AS UnpivotTable
After using Unpivot operator we get our original table back as we have successfully transformed the columns of the table back to rows:
- Difference between Structured Query Language (SQL) and Transact-SQL (T-SQL)
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL
- SQL | Procedures in PL/SQL
- SQL | AND and OR operators
- Combining aggregate and non-aggregate values in SQL using Joins and Over clause
- SQL | Functions (Aggregate and Scalar Functions)
- Basic SQL Injection and Mitigation with Example
- Performing Database Operations in Java | SQL CREATE, INSERT, UPDATE, DELETE and SELECT
- SQL | DDL, DQL, DML, DCL and TCL Commands
- SQL | DDL, DML, TCL and DCL
- SQL general functions | NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL and NANVL
- SQL using Python and SQLite | Set 2
- Difference between Static and Dynamic SQL
- Difference between Simple and Complex View in SQL
- Mean and Mode in SQL Server
- Sum and average of three numbers in PL/SQL
- Find the area and perimeter of right triangle in PL/SQL
- No. of vowels and consonants in a given string in PL/SQL
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.