August 18, 2008

Pivots – Converting Rows to Columns SQL Server 2005

Sometimes it is necessary to rotate results so that [the data in] columns are presented horizontally and [the data in] rows are presented vertically. This is known as creating a PivotTable, creating a cross-tab report, or rotating data.
Lets create table to use Pivot function.


CREATE TABLE TestPivot
( EmpID INT,
SalaryComponent INT,
SalaryAmount INT
)
go
INSERT INTO TestPivot
SELECT 1234, 1, 1000
UNION ALL
SELECT 1234, 2, 20
UNION ALL
SELECT 1234, 3, 4500
UNION ALL
SELECT 123, 1, 2000
UNION ALL
SELECT 123, 2, 30
UNION ALL
SELECT 123, 3, 4500
UNION ALL
SELECT 234, 1, 5000
UNION ALL
SELECT 234, 3, 4500
go

We need to show data like as follows:

EmpId Comp1 Comp2 Comp3 Comp4 Salary
123 2000 30 4500 0 6530
234 5000 0 4500 0 9500
1234 1000 20 4500 0 5520


This can be done using query:

SELECT EmpID,
SUM(CASE WHEN SalaryComponent = 1 THEN SalaryAmount ELSE 0 END) AS [Comp1],
SUM(CASE WHEN SalaryComponent = 2 THEN SalaryAmount ELSE 0 END) AS [Comp2],
SUM(CASE WHEN SalaryComponent = 3 THEN SalaryAmount ELSE 0 END) AS [Comp3],
SUM(CASE WHEN SalaryComponent = 4 THEN SalaryAmount ELSE 0 END) AS [Comp4],
SUM(SalaryAmount) AS Salary
FROM TestPivot
GROUP BY EmpID

let's see how to use PIVOT to do the same thing...

SELECT EmpID,
COALESCE([1],0) AS [Comp1],
COALESCE([2],0) AS [Comp2],
COALESCE([3],0) AS [Comp3],
COALESCE([4],0) AS [Comp4],
COALESCE([1],0)+COALESCE([2],0)+COALESCE([3],0)+COALESCE([4],0) as Salary
FROM
(
SELECT EmpID,SalaryComponent,SalaryAmount
FROM TestPivot
) as T
PIVOT (SUM(SalaryAmount) FOR SalaryComponent IN ([1],[2],[3],[4])) AS P
ORDER BY EmpID

PIVOT identifies the aggregate to be used, the column to pivot in the FOR clause, and the list of values that we want to pivot in the IN clause... in this case, the quarter number. Also notice that you must treat those as if they were column names. They must either be put in brackets or double quotes (if the quoted identifier setting is ON).
You must also bring EmpIDup as the row identifier in the pivot. Think of this as your "anchor" for the rows.

If you want a total for each row in the pivot, you can no longer use just an aggregate. Instead, you must add all the columns together.

Notice the NULL's where there are no values or where a NULL has been added into a total. Remember that anything plus a NULL is still a NULL. All of this occurs because the Pivot doesn't do any substitutions like the Case statements. To fix this little problem, we have to use COALESCE (or ISNULL) on the columns

No comments: