Sql server® 2012 t-sql fundamentals

TABLE 1-1

  Pivoted View of Total Quantity per Employee (on Rows) and Customer (on Columns)

empid

A

B

C

D

1

NULL

20

34

NULL

2

52

27

NULL

NULL

3

20

NULL

22

30

What you see in Table 7-1 is an aggregated and pivoted view of the data from the dbo.Orders 

table; the technique for generating this view of the data is called pivoting.

Every pivoting request involves three logical processing phases, each with associated elements: 

 a grouping phase with an associated grouping or on rows element, a spreading phase with an 

assoc iated spreading or on cols element, and an aggregation phase with an associated aggregation 

ele ment and aggregate function. 

In this example, you need to produce a single row in the result for each unique employee ID. This 

means that the rows from the dbo.Orders table need to be grouped by the empid attribute, and 

therefore the grouping element in this case is the empid attribute.

The dbo.Orders table has a single column that holds all customer ID values and a single column 

that holds their ordered quantities. The pivoting process is supposed to produce a different result 

column for each unique customer ID, and each column contains the aggregated quantities for that 

customer. You can think of this process as “spreading” quantities by customer ID. The spreading ele-

ment in this case is the custid attribute.

Finally, because pivoting involves grouping, you need to aggregate data to produce the result val-

ues in the “intersection” of the grouping and spreading elements. You need to identify the aggregate 

function (SUM, in this case) and the aggregation element (the qty attribute, in this case).

To recap, pivoting involves grouping, spreading, and aggregating. In this example, you group by 

empid, spread (quantities) by custid, and aggregate with SUM(qty). After you have identified the ele-

ments involved in pivoting, the rest is just a matter of incorporating those elements in the right places 

in a generic query template for pivoting.

This chapter presents two solutions for pivoting—a standard solution and a solution that uses a 

T-SQL–specific PIVOT operator.