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
- sql server: Estimated number of rows is way off
- SQL String Delimited by Comma into a Table
- How to delete large amount of data in SQL Server without data loss?
- circular relationship
- How do you set the name of a Azure SQL Server in C#?
- Cannot Bulk Load Error Code 3. Procedure working on one machine only
- How to convert operator = "eq-userid" statement from FetchXML into SQL code
- How do I address cases when a transaction completes in the database but a connectivity issue causes an exception?
- Deadlock in Update Stored Procedure
- ALTER and UPDATE tables in SQL based on two criteria
- Get top three records from duplicate records having unique identity
- Writing data to SQL database from ASP.NET textbox
- Replace value in column based on another column
- Combining row values from multiple tables into one result cell
- Left join using LINQ to SQL
- SQL Server - Distinct
- Why does the SQL SUM() function return a non-zero total when a group float values should add up to 0?
- Move emails to SQL Server
- SQL queries exceedingly slow when referencing primary key
- Except table with a conditions
- SQL Server : TOP 10 query by occurrence without using group by
- What is the use of CHECKDB command and why should we run it?
- conditional unique constraint
- SQL Server, get most recent record within a specified date range
- SQL combine multiple records into 1 line by date
- Msg 10300, Level 16, State 2, Line 1 in SQL Server 2014
- Windowed sum query giving a "because it is not contained in either an aggregate" error
- Generate a new Id column in SQL Server that will be dependent on other 2 id columns
- conversion failed when converting the varchar value 'another value' to data type int
- Can SQL Server table have a foreign key to a table that resolves to many records?
- update sql result set
- How to save an arbitrary DataTable to a new SQL Table
- A table without clustered index is considered as Heap Table?
- convert string to date in sql where dates are incomplete
- How to handle unique constraint exception to update row after failing to insert?
- Error Calling Stored Procedure from C#/ASP.NET
- Proper way to return value
- T-SQL error Could not find stored procedure
- Exclude multiple sum rows when all zero
- Passing parameter from vb.net using stored procedure
- Migrating from TFS 2010 to TFS 2012
- Is there a way to script diagrams in SQL 2000 (or save them another way)?
- Meaning of sp_who Status in SQL Server
- SSIS: Remove all rows from outputbuffer
- android app connects to external database
- Should I use In memory SQL (Hekaton) for queue messaging system?
- SQL server XML Parsing to Table
- PHP MS SQL - Multiple value one field pass in a stored procedure
- BULK insert parameterize FIRSTROW
- Cumulative Column that starts at Zero every Year
- How to get column values comma seperatedly
- SQL Case Statement (Understanding)
- Stored procedure keeps running - code works fine executed outside of the stored procedure
- The type or namespace name 'Smo' does not exist in the namespace 'Microsoft.SqlServer' (are you missing an assembly reference?)
- How can compare if two consecutive rows are arbitrary in SQL Server?
- SQL: Updating a column based on subquery results
- Why SQL ignores additional spaces in the end of a string?
- How do I get SAS to return a value if SQL stored procedure ran properly?
- What Causes "Internal connection fatal errors"
- Ifexists() tsql looping issue
- T-SQL USE satetement in condition
- Reservations SQL query
- Unpivot ALL Columns in a SQL Table
- Function cannot be used in the PIVOT operator because it is not invariant to NULLs
- Can't call stored procedure
- How to make Ranking based on Year and Month?
- How to supply parameters to a stored procedure
- How to query all values from 1rst table and fill with 2nd table
- Conditional row selection
- How to get all the columns of the MS SQL including view columns as well
- Remove duplicate data from query results
- Installing sql server express: VS2008 sp1 problem
- Insert, Update and Delete using stored Procedure
- Select the last entry recorded in a table for each day, within a duration of days
- How to store Executable sql task result in excel file ssis
- SQL's "Insert Into" is Working..... but not
- Importance / use / advantages of creating database diagram in sql server
- Comparing the length of two similar strings and picking the longest
- How do I retrieve external data from MS SQL from a Wordpress Blog?
- SQL Query with two conditions
- Is there any way/tool to identify estimate query run time in SQL sERVER
- SQL parse delimited string
- create rank() or row_number() column
- Case insensitive search in Microsoft SQL Server
- Is there a better way than dates to correlate the state of two databases?
- Sql Server - Invalid query because of no aggregate function or group by clause for comparing difference between 2 dates
- When SSIS is saving a PDF from an SSRS, the PDF is corrupted
- SQL Server transactions and transaction isolation - getting errors that I don't know how to fix
- Connect SQL Server 2008 to Outlook Calendar?
- Development and Test Environment Best Practices?
- Cannot convert Date in SSIS Expression builder
- How Do I Automate Database Schema comparison and Synchronization Process for SQL Server Database?
- Group by with linked conditions
- I want to move data from SQL server DB to Hbase/Cassandra etc.. How to decide which bigdata database to use?
- How to use insert and update command using c#
- Running dymanic SQL stored procedure results in catching syntax alert on other line
- Am I closing this SQL connection in a C# function appropriately?
- Update operation with substring in MSSQL
- Prevent INSERTing duplicated references to category table with Entity Framework
- Speeding up database access
- MySQL / SQL Server... working together as a total solution
- How to fix "The master key file does not exist or has invalid format." in SQL Server 2017
- parameter sniffing
- Import XML with Attribute to SQL Server Table
- How do I decrease the connect timeout with Entity Framework?
- Table column split to two columns in sql?
- TSQL how to concatenate horizontally inside the join,
- Automatically Drop Old Stored Procedure in SQL Server
- Deleting rows in a table a chunk at a time
- Removing duplicates from SQL Server table
- SQL GROUP BY with FILTER IN SELECT
- Generate Next Alphanumeric Code on SQL Server
- Join tables on dates when dates are different
- SSDT schema comparison error: Value cannot be null. Parameter name: identifierGroup
- Unpivot table in MySQL
- How to build RUNAS /NETONLY functionality into a (C#/.NET/WinForms) program?
- SQL Azure Geo Replication for non-redunancy purposes
- Any good way to enforce not null many-to-many relationship?
- How to convert table columns to rows
- Primary Key when storing a lot of data for a short period of time
- Need To combine two date parts into a new date field without the results adding
- I cannot send/insert data into my new database table using Visual Studio
- Maximum number of databases supported by SQL Server 2014 editions
- SQL refusing connection in load test
- SQL Comparing two attributes as 1 combination
- Pull Data from Text File Before Appending it to Table in SQL-Server 2005 DTS
- SQL - Query help
- T-SQL for combining 2 columns' values into one with "replicate"
- Combining two UPDATE statement with one SET statement with WHERE clause and one SET without
- Star Schema Structure - To many Dimensions
- Possible recursive CTE query using date ranges
- Is there any disadvantage to using PRINT in my stored procs?
- output clause VS triggers
- how to call table-valued function in CASE expression
- How to query a SingleResult object in a ASP.NET based server?
- How to append to a large varchar(max) column efficiently
- SSIS 2008 Flat File Source Deployment
- SQL query to find missing values in tables
- Coding for an SQL server in a local project
- Understanding simple stored procedure: Moving output to a variable
- Showing a progress bar for a database insertion
- How do I link two values in a nested SQL SELECT query?
- Insert zero between two IDs and make it length 10 in Stored Procedure
- Synchronize access to static variables in sql server SQLCLR
- Select earliest date for each Contact
- SQL Contains - only match at start
- Parallel query worker thread was involved in a deadlock
- IN CLAUSE CONTAIN MANY VALUES - TSQL
- Import a single column dataset (CSV or TXT or XLXS) to act as a list in SQL WHERE IN clause
- What is the mathematical way of calculating PERCENT_RANK() ,CUME_DIST() ,PERCENTILE_CONT() and PERCENTILE_DISC()