Sql Server pivot rows to columns

Hello, welcome to Appsloveworld Technologies, before continuing with the session, I strongly recommend reading below post.

• Sql Group By
• What is CTE?

what’s pivot operator?

Pivot operator is used to turn unique values from one column  into multiple columns in the output, thereby effectively rotating a table.Let’s understand what we mean by this definition with an example.

I have table TblOrganizationInfo, which has got three columns, Department, EmployeeName, and Salary.

If you look at the data, we have three travel agents, Jacob, Michael, and Luke inventory booking 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. We have spoken about the group by in detail in this post.

• Sql group by concept

if you new to the group by I strongly suggest reading above post first.

So in the output, we want Booking Location, Travel agent, and total Booking amount.So in the select clause, Location, TravelAgent, some of the booking amount as total. we get all the three columns fromTblOrganizationInfo 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 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, is 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.

so it’s the same data except that we are presenting  in a crosstab format and 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 column values for the location column.

But then we are converting these columns 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 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 Pivot Operator does and which gives us the effect of rotating a table.

The post Sql Server pivot rows to columns appeared first on Software Development | Programming Tutorials.