Pivot Rows to columns In Sql Server

What’s pivot operator?

The pivot operator is used to transform unique values from one column into multiple columns in the output, effectively rotating a table. Let’s understand this concept with an example.

Before continuing with the session, I strongly recommend reading below post.

 Sql Group By

• What is CTE?

I have a table named TblOrganizationInfo, which contains three columns: Department, EmployeeName, and Salary.

If you examine the data, we have three travel agents—Jacob, Michael, and Luke—making inventory bookings in four different countries: USA, UK, France, and India.

Table Script

CREATE TABLE [dbo].[TblOrganizationInfo](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Location] [nvarchar](50) NULL,
    [TravelAgent] [nvarchar](50) NULL,
    [BookingAmount] [float] NULL,
 CONSTRAINT [PK_TblOrganizationInfo] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblOrganizationInfo] ON 
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (1, 'France', 'Michael', 2000)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (2, 'USA', 'Jackson', 2200)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (3, 'UK', 'Jacob', 3000)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (4, 'India', 'Luke', 2500)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (5, 'USA', 'Michael', 1500)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (6, 'France', 'Jacob', 1000)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (7, 'India', 'Luke', 1200)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (8, 'USA', 'Jacob', 1700)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (9, 'UK', 'Michael', 1900)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (10, 'France', 'Jacob', 2100)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (11, 'USA', 'Luke', 2500)
GO
INSERT [dbo].[TblOrganizationInfo] ([Id], [Location], [TravelAgent], [BookingAmount]) VALUES (12, 'India', 'Michael', 3000)
GO
SET IDENTITY_INSERT [dbo].[TblOrganizationInfo] OFF
GO

We also have the Booking Amount on the same table. Now, let’s say we want to write a query which returns the total Booking amount by country and by the travel agent.

So obviously, if we have to achieve this, we can make use of the GROUP BY clause. If you're new to the GROUP BY, I strongly suggest reading the above post first.

So in the output, we want Booking Location, Travel agent, and total Booking amount. So in the SELECT clause, Location, TravelAgent, and the sum of the booking amount as total. We get all the three columns from TblOrganizationInfo Table. Group, first by Location and then by TravelAgent, order by in the same order Location first and then TravelAgent.

Select Location, TravelAgent, SUM(BookingAmount) as Total
from TblOrganizationInfo
group by Location, TravelAgent
order by Location, TravelAgent

Now, I would say this data can be better presented in a crosstab format. Let’s understand what we mean by a crosstab format.

If you look at this, Jacob has made a total booking of 3100 in France. And if you look at the way the same data is presented below, it's in a crosstab format.

So, Jackson made a total booking of 2200 in the USA. Along the same lines, Jacob made a total booking of 1700 in the USA. So, Tom.

It’s the same data, except that we are presenting it in a crosstab format. To achieve this, we are making use of the pivot operator.

Select TravelAgent, France, India, UK, USA
from
(
Select TravelAgent, Location, BookingAmount from TblOrganizationInfo
) as SourceTable
Pivot
(
Sum(BookingAmount) for Location in (France, India, UK,USA)
) as PivotTable

Query Explanation

So let’s see what’s happening here. Now, if you look at France, India, USA, UK, these are actually values from the Location column.

But then we are converting these values into column names in the output. And if you look at the definition of the pivot operator, Pivot is a SQL server operator that can be used to turn unique values from one column.

So, unique values from one column, Location, into multiple columns in the output. If you look at the image, we are taking the unique values of the Location column: France, India, US, UK, and turning them into column names in the output. And that’s what the Pivot Operator does, giving us the effect of rotating a table.