I recently explained how to write a PIVOT query to a group of new T-SQL developers. I have to admit that the syntax is complicated enough, and I use PIVOT infrequently enough, that I always have to look up how to write it. The syntax is confusing, but PIVOT is not really that difficult if you take it a step at a time.

A PIVOT query turns one of the columns in a base query into column headers. The DBA who organized the training class had an example concerning benefits data. She needed to come up with a list of employees with the benefit names listed across the top. Her original data and results might look something like this:

Here is the code to create the #benefits table:

CREATE TABLE #benefits (EmployeeINT,AmtINT,BenefitVARCHAR(10));
INSERT INTO #benefits (Employee,Amt,Benefit)
VALUES (101, 150,‘Med’),(101, 30,‘Dental’),(101, 400,‘401K’),
(102, 500,‘401K’),(103, 300,‘Med’),(103, 60,‘Dental’),(103, 100,‘401K’);

Step 1: Write the base query. This example is pretty simple, but it could involve a more complex query with joins, unions, sub-queries, or just about anything else you can think of. It might make sense to create a view of the base query to simplify the PIVOT query. Here is the base query for this example:

SELECT Employee, Amt, Benefit
FROM #benefits;

Step 2: Figure out from the results of the base query the three parts you will need, the row data, the aggregated data, and the pivoted column.

Step 3: Turn your base query into a derived table listing the row data columns in the outer select.

SELECT Employee
FROM (SELECT Employee, Amt, Benefit
      FROM #benefits) AS A;

Step 4: Add the PIVOT function.  Specify the aggregated data in this function.

SELECT Employee
FROM (SELECT Employee, Amt, Benefit
FROM #benefits) AS A
PIVOT(
SUM(AMT) 

Step 5. Specify the column that will be pivoted.

SELECT Employee
FROM (SELECT Employee, Amt, Benefit
FROM #benefits) AS A
PIVOT(
SUM(AMT) 
    FOR Benefit

Step 6. List the pivoted values and give the PIVOT expression an alias. At this point you can run the query, but you still won’t see the pivoted columns. It is important to understand that any pivoted values you leave out will not appear in the query.

SELECT Employee
FROM (SELECT Employee, Amt, Benefit
FROM #benefits) AS A
PIVOT(
SUM(AMT) 
    FOR Benefit
IN([Med],[Dental],[401K])
) AS PVT;

Step 7. List the pivoted values in the outer SELECT. Run the query to see the results

SELECT Employee,[Med],[Dental],[401K]
FROM (SELECT Employee, Amt, Benefit
FROM #benefits) AS A
PIVOT(
SUM(AMT) 
    FOR Benefit
IN([Med],[Dental],[401K])
) AS PVT;

Now you may want to do some tweaking to improve the results. In this example, I would like to change the NULL values to zeros. I can use the COALESCE function to accomplish this.

SELECT Employee, COALESCE([Med],0) AS [Med],
COALESCE([Dental],0) AS [Dental], COALESCE([401K],0) AS [401K]
FROM (SELECT Employee, Amt, Benefit
FROM #benefits) AS A
PIVOT(
    SUM(Amt)
FOR Benefit
IN([Med],[Dental],[401K])
) AS PVT;

You can also add an ORDER BY clause at the end. Include any of the column names in the SELECT list.

So, it is not really that difficult to write a PIVOT query. The key is to make sure that the base query contains everything you need. Simplify it with a view if that makes sense. Then identify the three parts you will need.

;