How to use PIVOT

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:

pivot1

 

 

 

 

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.

pivot2

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;

pivot 4

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.

 

 

 

 

 

 

;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

About Kathi Kellenberger

I am a SQL Server professional working for Linchpin People. I love talking about SQL Server to anyone who will listen, just ask my two year old granddaughter. I love to write and teach. I am so humbled and honored to say that I made SQL Server MVP in 2013.
This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s