Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Sunday, 26 February 2017

Pivot In SQL Server With Example

 No comments   


Introduction

In this article i will explain about what is Pivot and why we need to use Pivot in SQL server. I will explain this by comparing group by and pivot in the same scenario for better understanding of Pivot.


Why Do We Need PIVOT

Pivot is use for convert or transform row level data into column data. Many time when we working with dynamic queries and also in our simple scenerios we need to display our row level data as column level, for this we use Pivot and for performing just opposite operation SQL provides another keyword Unpivot. 
In other words pivot rotates you rows into columns and unpivot rotates you column into rows again.


Practice


CREATE TABLE EmployeeAccount 
(
ID int IDENTITY(1,1), 
Employee varchar(50), 
Country varchar(50),
Salary decimal(10,2)
)


After creation of table i have inserted few data for execution. Lets have a look how my data table looks like
Data of my table is not in a readable in first look and we need to do some calculation onto it.
What happend if i want total of perticular employee salary based on their respective Country. So here i am using Group By clause to do this operation.


SELECT Employee, Country, SUM(Salary) AS EmpSalary FROM EmployeeAccount Group BY Country, Employee 
ORDER BY Country, Employee


 

By using Pivot
If you notice group by returns no of rows which is also returned by our previous select query but if we want all the Employee related detail in a single row based on their salary total so how can we manage this. The answer is simple by using Pivot clause. Pivot gave's you access to rotate your rows into columns. So here i am displaying Country Names as Column names for each Employee .Have a look 

SELECT Employee, IND, UK, USA FROM
(
 SELECT Employee,Country, Salary FROM EmployeeAccount
) as tempTable

PIVOT
(
SUM(Salary) for Country IN([IND], [UK], [USA] ))
As PivotTable

After doing this you find a simple output on your result window like this



It is clearly shown that we transform our row data country names as new columns.

  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS Scope_Identity() Use of indexes comma seperated string in sql row_number() and dense_rank() salary table structure. while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write