PIVOT and UNPIVOT in SQL

What is PIVOT in SQL?

The PIVOT operator in SQL transforms a table into another form to get another view of the table. In simple terms, PIVOT converts the row data of the table into column data. It converts the unique values of one table column into multiple columns of the final table. Also, it runs aggregation formulations on the column values to be presented in the final result.

So, it helps convert row data into column data of a table suitable for analysis. But there are certain disadvantages to it too. Like, it is not a standard SQL feature. Thus it is implemented in various ways on different platforms. Also, it is not dynamic and thus works with limited flexibility.

Let us see the syntax of the PIVOT operator in SQL. Also, if some values are NULL in the table, the user needs to be more careful since NULL values behave unexpectedly in aggregations.

Syntax

Let us see the syntax of the PIVOT operator in SQL.

SELECT <columns>
FROM <table_name>
PIVOT(
Aggregation_Function (Column_to_aggregate)
FOR PivotColumn
IN (PivotColumn values)
) AS <pivot_table_alias>

Example

Now, let us study with an example. Let us make a table named in SQL using the following commands.

CREATE TABLE GLOBAL_DATA(
     NAME VARCHAR(20),
     COUNTRY VARCHAR(30),
     SCORE INT
     );
INSERT INTO GLOBAL_DATA
     VALUES
     ("SAM", "AUSTRALIA", 200),
     ("FREDDY", "USA", 100),
     ("RAHUL", "INDIA", 800),
     ("SHYAAM", "INDIA", 900),
     ("JAMES", "AUSTRALIA", 300),
     ("JULIA", "AUSTRALIA", 400),
     ("SAM", "INDIA", 400),
     ("FREDDY", "AUSTRALIA", 500),
     ("RAHUL", "USA", 300),
     ("SHYAAM", "USA", 200),
     ("SHYAAM", "AUSTRALIA", 150),
     ("SAM", "USA", 380),
     ("FREDDY", "INDIA", 420),
     ("RAHUL", "AUSTRALIA", 390),
     ("JAMES", "USA", 330),
     ("JULIA", "INDIA", 990),
     ("JAMES", "INDIA", 290),
     ("JULIA", "USA", 550);

SELECT * FROM GLOBAL_DATA;

OUTPUT

Now, let us apply the PIVOT operator on the table using the following commands:

SELECT NAME, AUSTRALIA, USA, INDIA
FROM GLOBAL_DATA
PIVOT (
    SUM(SCORE)
    FOR COUNTRY IN ([AUSTRALIA], [USA], [INDIA])
) AS FINALTABLE;

OUTPUT

Thus, we can see that all the names now have different attribute values concerning other countries in the original table, with their scores as their values. It helps improve the readability of the table whose name has how much score in each country.

Also see, SQL EXCEPT

What is UNPIVOT in SQL?

The UNPIVOT operator in SQL does the opposite of what the PIVOT operator does. It means that the UNPIVOT operator converts the columns into rows.

It is useful when the table is too broad and needs to be transformed. Thus, it also helps reduce the amount of storage required in some cases. But there are certain limitations to it too. It increases the number of rows significantly, sometimes making the table too long. If there is duplicity in data entries, it might create data integrity issues. Also, both PIVOT and UNPIVOT in SQL are bounded to limited support.

Syntax

Let us see the syntax of UNPIVOT in SQL.

SELECT <columns>
FROM <table_name>
UNPIVOT(
Aggregation_Function (Column)
FOR PivotColumn
IN (PivotColumn values)
) AS <unpivot_table_alias>

Example

Let's try to use the UNPIVOT operator on the table on which we used the PIVOT operator just a while ago. First, we will use the PIVOT operator on it and then the UNPIVOT operator to get the table again.

SELECT NAME, COUNTRY, SCORE
FROM
(
SELECT NAME, AUSTRALIA, USA, INDIA FROM
GLOBAL_DATA
PIVOT
(
SUM(SCORE)
FOR COUNTRY IN ([AUSTRALIA],[USA],[INDIA])
) AS PIVOTTABLE
) P
UNPIVOT
(
SCORE FOR COUNTRY IN ([AUSTRALIA],[USA],[INDIA])
) AS UNPIVOTTABLE;

OUTPUT

Here, we can see that the table has been created again with the default columns NAME, COUNTRY, and SCORE. This way, we can say that the UNPIVOT operator converted the columns into rows.

Also see, Natural Join in SQL

Frequently Asked Questions

What is SQL?

SQL stands for Structured Query Language. A relational database consists of data in the form of tables, and it is a database-oriented programming language that helps access, add, update, and delete data. It is advantageous and consists of many operators.

What is PIVOT in SQL?

The PIVOT operator in SQL transforms a table into another form. In simple terms, PIVOT converts the row data of the table into column data. It converts the unique values of one table column into multiple columns of the final table.

What is UNPIVOT in SQL?

The UNPIVOT operator in SQL does the opposite of what the PIVOT operator does. The UNPIVOT operator converts the columns into rows. It gives the initial table if applied after a specific PIVOT operator query for the same aggregation on the exact attributes.

What is the difference between PIVOT and UNPIVOT in SQL?

The main difference between PIVOT and UNPIVOT in SQL is that the PIVOT operator converts the row data of the table into column data. And the UNPIVOT operator converts the table's column data into row data.

Conclusion

SQL is an excellent language for running queries on data in a relational database. It consists of different types of commands, operators, and queries. In this article, we studied two famous operators in SQL. We looked at PIVOT and UNPIVOT in SQL. We learned their meaning and also looked at their implementations.

If you want to learn more about this topic, do read the following articles:-

• SQL Introduction
   
• LIMIT in SQL
   
• LPAD in SQL
   
• DATEPART in SQL
   
• Sql functions

To learn more about DSA, competitive coding, and many more knowledgeable topics, please look into the guided paths on Codestudio. Also, you can enroll in our courses and check out the mock test and problems available. Please check out our interview experiences and interview bundle for placement preparations.

Happy Coding!