Sql server® 2012 t-sql fundamentals

pivoting with Standard SQL

The standard solution for pivoting handles all three phases involved in a very straightforward manner. 

The grouping phase is achieved with a GROUP BY clause; in this case, GROUP BY empid. 

www.it-ebooks.info

---

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 225

The spreading phase is achieved in the SELECT clause with a CASE expression for each target col-

umn. You need to know the spreading element values ahead of time and specify a separate expres-

sion for each. Because in this case you need to “spread” the quantities of four customers (A, B, C, and 

D), there are four CASE expressions. For example, here’s the CASE expression for customer A.

CASE WHEN custid = 'A' THEN qty END

This expression returns the quantity from the current row only when the current row represents 

an order for customer A; otherwise the expression returns a NULL. Remember that if an ELSE clause 

is not specified in a CASE expression, the default is ELSE NULL. This means that in the target column 

for customer A, only quantities associated with customer A appear as column values, and in all other 

cases the column values are NULL.

If you don’t know the values that you need to spread by ahead of time (the distinct customer IDs 

in this case) and you want to query them from the data, you need to use dynamic SQL to construct 

the query string and execute it. Dynamic pivoting is demonstrated in Chapter 10, “Programmable 

Objects.”

Finally, the aggregation phase is achieved by applying the relevant aggregate function (SUM, in 

this case) to the result of each CASE expression. For example, here’s the expression that produces the 

result column for customer A.

SUM(CASE WHEN custid = 'A' THEN qty END) AS A

Of course, depending on the request, you might need to use another aggregate function (such as 

MAX, MIN, or COUNT).

Here’s the complete solution query that pivots order data, returning the total quantity for each 

employee (on rows) and customer (on columns).

SELECT empid, 

  SUM(CASE WHEN custid = 'A' THEN qty END) AS A, 

  SUM(CASE WHEN custid = 'B' THEN qty END) AS B, 

  SUM(CASE WHEN custid = 'C' THEN qty END) AS C, 

  SUM(CASE WHEN custid = 'D' THEN qty END) AS D   

FROM dbo.Orders 

GROUP BY empid;

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