Using Pivot in Sql Server

Pivot Option is used when you to rotate a table. That means whenever there are a number of rows and we want to show the values of a specified column into column names we can use Pivot. Pivot will help you to change you data rows into columns. It was introduced in SQL Server 2005. We can achieve the same functionality with the help of CASE statements and GROUP by. But PIVOT takes lesser amount of coding. Lets us take an Example :

Suppose we following table Sales :


Day Items Amount
Mon I1 100.00
Tue I2 50.00
Mon I1 25.00
Mon I2 300.00
Tue I2 500.00

Output desired :

Day I2 I1
Mon 300.00 125.00
Tue 550.00

SELECT Day, [I2] AS I2, [I1] AS I1
FROM
(SELECT Day, Items, Amount
FROM Sales ) s
PIVOT
(
SUM (Amount)
FOR Items IN
( [I2], [I1])
) AS pvt


Now there are cases where we dont want to use any aggregate function. Lets take an example below :

Suppose we have a table EMP

Primary EmpNo Designation
key(ID) (DID)
1 101 M
1 103 D
1 102 L
3 104 M
3 105 D
3 106 L

now we need to show the result as follows :

M D L
1 101 103 102
3 104 105 106

Using Pivot following will be the query :

SELECT ID, [M],[D],[L]
FROM (
SELECT ID, EmpNo,DID
FROM EMP) up
PIVOT ( min(EmpNo) FOR DID IN ('[M]','[D]','[L]')) AS pvt

min would suffice here as the aggregate function to get us the desired result.

Limitations of PIVOT over CASE :

1) we need to hardcode the columns while using PIVOT. Though we can use Dynamic SQL if we are not sure of the number of columns that we need.

2)We can use only one aggregate field in PIVOT whereas in CASE we can aggregate any number of fields we want.