Total Page Preview: 000000007650
PIVOT and UNPIVOT in SQL Server with Example
In this article we will learn convert row into the coloumn using PIVOT and convert coloumn in the row using UNPIVOT in sql server.
PIVOT Operator:
PIVOT operator which was introduced in SQL Server 2005. PIVOT relational operator convert data from row level to column level. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. Using PIVOT operator we can perform aggregate operation where we required.
Example:
-----------Create Product Table----------
CREATE TABLE [dbo].[tblProducts]
(
[ProductName] [varchar](50) NULL,
[Quantity] [int] NULL,
[City] [nvarchar](50) NULL
) ON [PRIMARY]
-------------------------Insert data into the Product talbe----------------------------------
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Laptop', 10, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Mobile', 12, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Desktop', 22, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Laptop', 23, N'Delhi')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Mobile', 34, N'Delhi')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Desktop', 7, N'Delhi')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Laptop', 87, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Mobile', 66, N'Noida')
INSERT [dbo].[tblProducts] ([ProductName], [Quantity], [City]) VALUES (N'Laptop', 45, N'Delhi')
------------------ Get all product records-------------------
SELECT City, ProductName, Quantity FROM tblProducts
------------------ Pivot on Product name column using order by city-------------------
SELECT City, Laptop, Mobile, Desktop
FROM (
SELECT City, ProductName, Quantity
FROM tblProducts
) AS product
PIVOT
(
SUM(Quantity)
FOR ProductName IN (Laptop, Mobile, Desktop)
) AS pvt ORDER BY City
OUTPUT:
UNPIVOT Operator:
The UNPIVOT operator which was introduced in SQL Server 2005. UNPIVOT relational operator is reverse process of PIVOT relational operator. UNPIVOT relational operator convert data from column level to row level.
Example:
-----------Create Product Table----------
CREATE TABLE [dbo].[ProductQuintity]
(
[City] [varchar](50) NULL,
[Laptop] [int] NULL,
[Desktop] [int] NULL,
[Mobile] [int] NULL
) ON [PRIMARY]
-------------------------Insert data into the Product talbe----------------------------------
INSERT [dbo].[ProductQuintity] ([City], [Laptop], [Desktop], [Mobile]) VALUES (N'Noida', 23, 65, 45)
INSERT [dbo].[ProductQuintity] ([City], [Laptop], [Desktop], [Mobile]) VALUES (N'Delhi', 45, 34, 65)
INSERT [dbo].[ProductQuintity] ([City], [Laptop], [Desktop], [Mobile]) VALUES (N'Mumbai', 45, 76, 76)
------------------ Get all product records-------------------
SELECT City, Laptop, Desktop, Mobile FROM ProductQuintity
--------Convert (Laptop, Desktop and Mobile) coloumn into the row using UNPIVOT--------
SELECT ProductName, TotalQuintity, City
FROM ProductQuintity
UNPIVOT
(
TotalQuintity
FOR ProductName IN (Laptop, Mobile, Desktop)
) AS pvt ORDER BY ProductName
OUTPUT:
Thank You,
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com