Using Pivot in Sql Server

Hi All,

First of all we need to know about pivot in sql server.

pivot rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

The following is the syntax for Pivot in sql.

SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
.............................
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column], .............................)
) AS <alias for the pivot table>
<optional ORDER BY clause>;

Let’s take a basic example so we can understand it easily.

There is a table “Employee” with below columns,
ID : autogenerate int NOT NULL
name : nvarchar(50) NOT NULL
address : nvarchar(250) NOT NULL
contactNo : nvarchar(50) NOT NULL
leaveType : nvarchar(2) NOT NULL

You can create it by using below query

GO
CREATE TABLE [dbo].[Employee](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) NOT NULL,
	[address] [nvarchar](250) NOT NULL,
	[contactNo] [nvarchar](50) NOT NULL,
	[leaveType] [nvarchar](2) NOT NULL
) ON [PRIMARY]
GO

Now insert some records in table using below query

INSERT INTO Employee(name, address, contactNo, leaveType)VALUES
('sachin', 'aaa', '111111111', 'PL'),  
('sehwag', 'bbb', '222222222', 'CL'),
('dravid', 'ccc', '333333333', 'SL'),  
('sehwag', 'bbb', '222222222', 'CL'),
('sachin', 'aaa', '111111111', 'SL'),
('Yuvraj', 'ddd', '444444444', 'PL'),
('Virat', 'eee', '555555555', 'SL'),
('Yuvraj', 'ddd', '444444444', 'CL')

now we need to count leave of all employee by leaveType and need to display it as below
name   address   contactNo   CL   PL   SL

see below query which use pivot feature and get the above result:

SELECT name, address, contactNo, CL, PL, SL
FROM (
SELECT name, address, contactNo, leaveType
FROM Employee) srcTable
PIVOT (COUNT(leaveType) FOR leaveType IN (CL, PL,SL)) AS pvtTable
ORDER BY name

see the below result
piv_result
Hope this will clear the concept of pivot feature.

One thought on “Using Pivot in Sql Server

  1. Nice post. good code.

    One of the things that you might want to demonstrate how to do with this same example is how to produce row and column totals for the LeaveType’s.

Leave a Reply

Your email address will not be published. Required fields are marked *


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>