Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 227

empid       A           B           C           D 

----------- ----------- ----------- ----------- ----------- 

2           12          NULL        NULL        NULL 

1           NULL        20          NULL        NULL 

1           NULL        NULL        14          NULL 

2           NULL        12          NULL        NULL 

1           NULL        NULL        20          NULL 

3           10          NULL        NULL        NULL 

2           NULL        15          NULL        NULL 

3           NULL        NULL        22          NULL 

3           NULL        NULL        NULL        30 

2           40          NULL        NULL        NULL 

3           10          NULL        NULL        NULL 

 

(11 row(s) affected)

Because orderid is part of the grouping elements, you get a row for each order instead of a row for 

each employee. The logical equivalent of this query that uses the standard solution for pivoting has 

orderid, orderdate, and empid listed in the GROUP BY list as follows.

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 orderid, orderdate, empid;

I strongly recommend that you never operate on the base table directly, even when the table con-

tains only columns used as pivoting elements. You never know whether new columns will be added to 

the table in the future, rendering your queries incorrect. I recommend considering the use of a table 

expression as the input table to the PIVOT operator as if it were part of the requirement of the opera-

tor’s syntax. 

As another example of a pivoting request, suppose that instead of returning employees on rows 

and customers on columns, you want it the other way around: the grouping element is custid, the 

spreading element is empid, and the aggregation element and aggregate function remain SUM(qty). 

After you learn the “template” for a pivoting solution (standard or native), it’s just a matter of fitting 

those elements in the right places. The following solution query uses the native PIVOT operator to 

achieve the result.

SELECT custid, [1], [2], [3] 

FROM (SELECT empid, custid, qty 

      FROM dbo.Orders) AS D 

  PIVOT(SUM(qty) FOR empid IN([1], [2], [3])) AS P;

The employee IDs 1, 2, and 3 are values in the empid column in the source table, but in terms of 

the result, these values become target column names. Therefore, in the PIVOT IN clause, you must 

refer to them as identifiers. When identifiers are irregular (for example, when they start with a digit), 

you need to delimit them—hence the use of square brackets. 

www.it-ebooks.info

---

228  

Microsoft SQL Server 2012 T-SQL Fundamentals

This query returns the following output.

custid    1           2           3 

--------- ----------- ----------- ----------- 

A         NULL        52          20 

B         20          27          NULL 

C         34          NULL        22 

D         NULL        NULL        30