Using SQL Pivot – SQL Tutorial

Using SQL Pivot – SQL Tutorial

4 mins read495 Views Comment
clickHere
Updated on Apr 29, 2022 13:00 IST

This article will cover what SQL PIVOT is, how to use SQL PIVOT, and what SQL UNPIVOT is, along with examples.

2022_04_SQL-vs-NoSQL-1.jpg

Often while handling databases, there comes a requirement to change the row-level data to columnar data. SQL Pivot is a simple function used to do the same. In this article, I will give you a detailed walkthrough of the SQL Pivot function, and an example of converting rows of a table into columns.

This article will cover the following topics:

  1. What is SQL PIVOT?
  2. How to use SQL PIVOT?
  3. What is SQL UNPIVOT?

In this article, I will consider the following table to explain the SQL PIVOT function.

Medications Table

MedID PendingExpiryDays Price PatientID PaymentID
1 30 567 123 PU1
2 20 345 682 PU2
3 10 632 821 PU3
4 15 654 682 PU1
5 25 123 821 PU3
6 35 876 123 PU1
7 40 987 821 PU2
8 5 234 682 PU2
9 45 654 821 PU3
10 60 145 123 PU2

What is SQL PIVOT?

As mentioned above, SQL PIVOT is used to convert the rows into columnar data. So, it can rotate the table values by changing a single column into multiple columns.

Syntax:

[code]
SELECT NonPivoted Column_Name,  
    [FIRST Pivoted Column_Name] AS Column_Name,  
    [SECOND Pivoted Column_Name] AS Column_Name, 
    ...  
    [LAST Pivoted Column_Name] AS Column_Name  
FROM 
    (SELECT query)   
    AS 
PIVOT  
(  
    [AggregationFunction](Column_Name)  
FOR  
[Column_Name]   
    IN ( [FIRST Pivoted Column_Name], [SECOND Pivoted Column_Name],
    ... [LAST Pivoted COLUMN]) AS;
[/code]
 

Example:

[code]
SELECT PaymentID, AVG(Price) AS AvgMedPrice   
FROM Medications  
GROUP BY PaymentID;
[/code]
 

Note: 

  1. You can use the ORDER BY and GROUP BY clauses with the SQL PIVOT function to sort and group the values based on requirements.
  2. While using aggregate functions, NULL values aren’t considered for computation.

Output:

PaymentID AvgMedPrice
PU1 699
PU2 427.75
PU3 469.66

Now, if you wish to change the rows of this table to columnar data, then the paymentID column values will become the column headers in the new table. Refer to the below code:

[code]
SELECT 'AvgMedPrice' AS MedPriceCost,
[PU1], [PU2], [PU3]
FROM
(SELECT PaymentID, Price
FROM Medications) AS Sample
PIVOT
(
AVG(Price)
FOR PaymentID IN ([PU1], [PU2], [PU3])
) AS FinalTable;
[/code]
 

Output:

MedPriceCost PU1 PU2 PU3
AvgMedPrice 699 427.75 469.66

Next, in this article, let us understand how to use the SQL PIVOT step by step.

How to use SQL PIVOT?

You can use SQL PIVOT to perform the required data operation by following the below steps:

  1. Choosing the columns to be pivoted
  2. Choose the initial/source table
  3. Apply the PIVOT function
  4. Mention the aggregate functions
  5. Apply the pivot values

Choosing the columns to be pivoted

The first step is to select the columns that need to be pivoted. In the above example, AvgMedPrice must be pivoted with the paymentIDs as the column headers.

[code]
SELECT 'AvgMedPrice' AS MedPriceCost,
[PU1], [PU2], [PU3]
[/code]
 

Choose the initial/source table

The next step is to choose the initial or the source table. The table selected is the table from which the data will be retrieved for pivoting. In our above example, I chose the PaymentID and Price to be retrieved from the Medications table.

[code]
(SELECT PaymentID, Price
FROM Medications) AS Sample
[/code]
 

Apply the SQL PIVOT function

Next, mention the keyword PIVOT to indicate to the system that a PIVOT operation has to be performed.

[code]
PIVOT
[/code]
 

Mention the aggregate functions

You have to mention the aggregate function for a column in the table. Here I will be using the AVG function to calculate the AVG price of the medicines.

[code]
AVG(Price)
[/code]
 

Apply the pivot values

Lastly, mention all the values that must be included in the final pivot table. Remember that the mentioned values will be the column headers in the final table.

[code]
FOR PaymentID IN ([PU1], [PU2], [PU3])
) AS FinalTable;
[/code]
 

Now that you have understood how to use the SQL PIVOT clause, let us know the SQL UNPIVOT clause.

What is SQL UNPIVOT?

SQL UNPIVOT is just the opposite of the SQL PIVOT clause. It changes the columnar data into row-level data. 

Syntax:

[code]
SELECT NonPivoted Column_Name,  
    [FIRST Pivoted Column_Name] AS Column_Name,  
    [SECOND Pivoted Column_Name] AS Column_Name, 
    ...  
    [LAST Pivoted Column_Name] AS Column_Name  
FROM 
    (SELECT query)   
    AS 
UNPIVOT  
(  
    [AggregationFunction](Column_Name)  
FOR  
[Column_Name]   
    IN ( [FIRST Pivoted Column_Name], [SECOND Pivoted Column_Name],
    ... [LAST Pivoted COLUMN]) AS;
[/code]
 

Example:

Let us consider the following table to understand the SQL UNPIVOT clause.

MedicineID Pharmacy1 Pharmacy2 Pharmacy3
123 42 131 89
421 12 76 764

To unpivot the above table, mention the following code:

[code]
SELECT MedicineID, Pharmacies, NumberofMedicines
FROM
(SELECT MedicineID, Pharmacy1, Pharmacy2, Pharmacy3
FROM InitialTable) X
UNPIVOT
(NumberofMedicines FOR Pharmacies IN
(Pharmacy1, Pharmacy2, Pharmacy3)
)AS FinalTable;
[/code]
 

Output:

MedicineID Pharmacies NumberofMedicines
123 Pharmacy1 42
123 Pharmacy2 131
123 Pharmacy3 89
421 Pharmacy1 12
421 Pharmacy2 76
421 Pharmacy3 764

With this, we end this article on SQL PIVOT and SQL UNPIVOT. We hope you found it informative. You can refer to the article on MySQL Commands for a detailed walkthrough of all commands.

Top Trending Tech Articles:
Career Opportunities after BTech | Online Python Compiler | What is Coding | Queue Data Structure | Top Programming Language | Trending DevOps Tools | Highest Paid IT Jobs | Most In Demand IT Skills | Networking Interview Questions | Features of Java | Basic Linux Commands | Amazon Interview Questions

Recently completed any professional course/certification from the market? Tell us what liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio

Comments