Understanding the PIVOT function

The PIVOT function is very useful but not easily understood at first. I thank Itzik Ben-Gan, Dejan Sarka and Ron Talmage and their study guide Exam 70-461: Querying Microsoft SQL Server 2012 for the brilliant explanation.

Here’s their example replicated here. Remember there are 3 columns used in PIVOT. The Grouping column is what you want each row to be aggregated to. The Spreading column becomes the columns after PIVOTing, and the Aggregate columns are the measures.

You need a pivot aggregate function (SUM in this case) and COUNT(*) cannot be used but COUNT(<column>) can.

The reason a common table expression is used to select only the necessary columns is because all other columns beside the Spreading and Aggregate columns will become the Grouping element which may not necessary be what you want.

WITH PivotData AS

(
SELECT
custid , -- grouping column
shipperid, -- spreading column
freight -- aggregation column
FROM Sales.Orders
)
SELECT custid, [1], [2], [3]
FROM PivotData
PIVOT(SUM(freight) FOR shipperid IN ([1],[2],[3]) ) AS P;

Take this for example. If you run this example below, the result of the PIVOT is a single row from customer ID = 85.

-- CREATE TABLE AND FILL WITH SAMPLE DATA

DECLARE @orders TABLE
(
custid INT,
empid INT,
shipperid INT,
freight NUMERIC(10,2)
)

INSERT INTO @orders (custid, empid, shipperid, freight)
VALUES (85,5,3,32.38),
(85,6,1,6.01),
(85,2,2,1.15),
(85,2,2,7.79),
(85,3,3,11.08);

— PIVOT DATA —

WITH CTE AS (
SELECT custid
,shipperid
,freight
FROM @orders
)
SELECT custid,[1],[2],[3]
FROM CTE
PIVOT (SUM(freight) FOR shipperid IN ([1],[2],[3])) P;

Result:   

But if the empid column is included in the Common Table Expression, it is now one of the GROUPING columns.

WITH CTE AS (
SELECT custid
,empid
,shipperid
,freight
FROM @orders
)
SELECT custid,[1],[2],[3]
FROM CTE
PIVOT (SUM(freight) FOR shipperid IN ([1],[2],[3])) P;

Result:   

As you can see the result is different. Why? To answer that, add in the empid column in the final query.

WITH CTE AS (
SELECT custid
,empid
,shipperid
,freight
FROM @orders
)
SELECT custid,empid,[1],[2],[3]
FROM CTE
PIVOT (SUM(freight) FOR shipperid IN ([1],[2],[3])) P;

Result:   

It is clearer here that the aggregated columns are now grouped by columns custid and empid, WHICH may or may not be what you want. But now you know why. 🙂

If you need to use the PIVOT function without it being part of a CTE, click here.

6 thoughts on “Understanding the PIVOT function

  1. I’m very interested in reading your article because I’ve struggled with understanding how to use the Pivot function but your article is not all there and shows an error on the page. Can you please fix it or send me the link to the guts of the article?

  2. Thanks terry. This the simplest intro I have seen for pivoting. By the way, are the names grouping column, spreading column, aggregation column standard pivot terminology ? Perhaps you could mention the motivation for pivoting before you explain it. You could say – find me the total freight for each shipper id associated with each customer id like this custid, shipperId1, shipperId2, shipperId3 etc. If you don’t mention the second part, then the reader might wonder why you are using pivoting. They might wonder if could just use a group by instead –

    select custid, shipperid, sum(freight) as TotalFreight
    from orders — i saved it as a real table
    group by custid,shipperid

    1. Great site!I’m creating a gogole spreadsheet pivot chart and trying to remove the grand totals.I have data label called priority 1 in rows and the same in values so i can count each unique text and count the number of times one appears, and then show that in a pie chart. any ideas on how to remove the grand total? it is showing as a separate pie slice and making the whole pie off. Thank you,finn

Leave a Reply

Your email address will not be published.