Pivoting Data in SQL

Pivoting Data in SQL

Before Explaining you about Pivoting of Data I will tell you what's Pivoting  Mean

Pivoting Data Mean Changing State Of Rows to State of Columns means Displaying

Row Data as Column Names.

With Theory It's little bit hard to understand 

Let me Explain through Example

Create table Emp(empid int ,custid nvarchar(30),qty int )
insert into Emp values (1,'A',10) ,(2,'b',5),(3,'c',2),(4,'A',6),(5,'b',1),(6,'c',6),(7,'d',6), (1,'A',12) ,(2,'b',15)

--run this query to see group by result based on empid and Custid

select empid,custid,sum(qty) as Totalqty from emp group by empid,custid 

Output

empid custid Totalqty
1 A 22
4 A 6
2            b 20
5           b  1
3 c               2
6 c 6
7 d 6

Now you want to show empid along with custid as Column Names that will show total quantity as Data
by Using Pivot Operator

select empid,A,B,C,D   from (select empid,custid,qty from emp) As D
pivot(sum(qty) for custid in (A,B,C,D)) as P

Output

empid A B C D
1 22 NULL NULL NULL
2 NULL 20 NULL NULL
3 NULL NULL 2 NULL
4 6 NULL NULL NULL
5 NULL 1 NULL NULL
6 NULL NULL 6 NULL
7 NULL NULL NULL 6

Let me Explain this query
Empid,A,B,C,D are the values which you are Displaying
thr Pivot Operator will Sum the qty
based on those Custid which are inside In clause and show them as Column Names.

The Reason of putting emp table inside derived Table D as Pivot Operator doesn't explicitly
specify Grouping Element putting them inside Derived table has given us surity that it will only Group by Columns inside Derived otherwise it will group by all column names of a table.
In our table as we have only three column  so if we will write query without Derived table also
The Result will be same.

select empid,A,B,C,D    from emp
pivot(sum(qty) for custid in (A,B,C,D)) as P
order by empid

Output

empid A B C D 
1 22 NULL NULL NULL
2 NULL 20 NULL NULL
3 NULL NULL 2 NULL
4 6 NULL NULL NULL
5 NULL 1 NULL NULL
6 NULL NULL 6 NULL
7 NULL NULL NULL 6



Note: if you want to Group By some column always specify the column names inside Derived table
otherwise it will group by all columns of table
Secondly you can achieve this result also by Using Case expression.