Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 229

The table has a row for each employee; a column for each of the four customers A, B, C, and D; 

and the order quantity for each employee and customer in the employee-customer intersections. 

Notice that irrelevant intersections (employee-customer combinations that had no intersecting order 

activity) are represented by NULL marks. Suppose that you receive a request to unpivot the data, 

requiring you to return a row for each employee and customer, along with the order quantity. The 

resulting output should look like this.

empid       custid    qty 

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

1           B         20 

1           C         34 

2           A         52 

2           B         27 

3           A         20 

3           C         22 

3           D         30

In the following sections, I’ll discuss two techniques for solving this problem—a technique that fol-

lows the SQL standard and a technique that uses a T-SQL–specific UNPIVOT operator.