Using SQL Pivot – SQL Tutorial

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

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.