Hello, welcome to Appsloveworld Technologies, before continuing with the session, I strongly recommend reading below post.
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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?