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,
INSERT INTO TestPivot
SELECT 1234, 1, 1000
SELECT 1234, 2, 20
SELECT 1234, 3, 4500
SELECT 123, 1, 2000
SELECT 123, 2, 30
SELECT 123, 3, 4500
SELECT 234, 1, 5000
SELECT 234, 3, 4500
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:
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
GROUP BY EmpID
let's see how to use PIVOT to do the same thing...
COALESCE(,0) AS [Comp1],
COALESCE(,0) AS [Comp2],
COALESCE(,0) AS [Comp3],
COALESCE(,0) AS [Comp4],
COALESCE(,0)+COALESCE(,0)+COALESCE(,0)+COALESCE(,0) as Salary
) as T
PIVOT (SUM(SalaryAmount) FOR SalaryComponent IN (,,,)) 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