Introduction
SQL has relational operators like PIVOT and UNPIVOT. These relational operators help in transforming the table into a simple version and for a clear view of the data. PIVOT operator in SQL converts the row data of the table into column data. In contrast, UNPIVOT operator in SQL is opposite to PIVOT and converts the column data to row data in a table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.
Syntax
Below is the syntax to be followed to transform the table using PIVOT and UNPIVOT in SQL. For the PIVOT operator in SQL, the syntax is as follows:
The above syntax converts the row-wise data to column data.
For the UNPIVOT operator in SQL, the syntax is as follows:
Examples:
For a better understanding of PIVOT and UNPIVOT in SQL servers, the below examples are discussed:
Consider the below table named Department is present in the database. Now, using the PIVOT operator in SQL converts the row-wise data to column-wise data:
The query to be written to convert the data is as follows :
From the above query, we can observe that the columns are based on the given company names in the above table. In this way, a PIVOT operator is used in SQL. The output is as follows:
An example of UNPIVOT in SQL
For the above-transformed table, to convert it into the original table we need to use UNPIVOT operator in SQL. The query is as follows/
The output obtained from UNPIVOT operator in SQL is as follows:
Conclusion
The PIVOT and UNPIVOT operator in SQL is used to convert the row-wise data into column-wise and vice-versa. This article mainly explained the syntaxes involved in transforming the table. A few examples regarding the PIVOT and UNPIVOT in SQL are also explained.