Sql server® 2012 t-sql fundamentals

pivoting with the native T-SQL 

PIVOT

 Operator

SQL Server supports a T-SQL–specific table operator called PIVOT. The PIVOT operator operates in 

the context of the FROM clause of a query like other table operators (for example, JOIN). It operates 

on a source table or table expression, pivots the data, and returns a result table. The PIVOT operator 

involves the same logical processing phases as described earlier (grouping, spreading, and aggregat-

ing) with the same pivoting elements, but it uses different, native syntax. 

www.it-ebooks.info

---

226  

Microsoft SQL Server 2012 T-SQL Fundamentals

The general form of a query with the PIVOT operator is shown here.

SELECT ... 

FROM  

  PIVOT(() 

        FOR  

          IN ()) AS  

...;

In the parentheses of the PIVOT operator, you specify the aggregate function (SUM, in this ex-

ample), aggregation element (qty), spreading element (custid), and the list of target column names (A, 

B, C, D). Following the parentheses of the PIVOT operator, you specify an alias for the result table. 

It is important to note that with the PIVOT operator, you do not explicitly specify the grouping ele-

ments, removing the need for GROUP BY in the query. The PIVOT operator figures out the grouping 

elements implicitly as all attributes from the source table (or table expression) that were not specified 

as either the spreading element or the aggregation element. You must ensure that the source table 

for the PIVOT operator has no attributes besides the grouping, spreading, and aggregation elements, 

so that after specifying the spreading and aggregation elements, the only attributes left are those 

you intend as grouping elements. You achieve this by not applying the PIVOT operator to the original 

table directly (Orders in this case), but instead to a table expression that includes only the attributes 

representing the pivoting elements and no others. For example, here’s the solution query to the origi-

nal pivoting request, using the native PIVOT operator.

SELECT empid, A, B, C, D 

FROM (SELECT empid, custid, qty 

      FROM dbo.Orders) AS D 

  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

Instead of operating directly on the dbo.Orders table, the PIVOT operator operates on a derived 

table called D that includes only the pivoting elements empid, custid, and qty. When you account for 

the spreading element, which is custid, and the aggregation element, which is qty, what’s left is empid, 

which will be considered the grouping element.

This query returns the output shown earlier in Table 7-1.

To understand why you’re required to use a table expression here, consider the following query 

that applies the PIVOT operator directly to the dbo.Orders table.

SELECT empid, A, B, C, D 

FROM dbo.Orders 

  PIVOT(SUM(qty) FOR custid IN(A, B, C, D)) AS P;

The dbo.Orders table contains the attributes orderid, orderdate, empid, custid, and qty. Because the 

query specified custid as the spreading element and qty as the aggregation element, the remaining 

attributes (orderid, orderdate, and empid) are all considered the grouping elements. This query, there-

fore, returns the following output.

www.it-ebooks.info

---