Total Page Preview:   000000001865

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:

UNPIVOT In SQL Server

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



Comments


                           
                           

                           

                           

Facebook User: