Sql server® 2012 t-sql fundamentals

Unpivoting with Standard SQL

The standard solution to unpivoting involves implementing three logical processing phases in a very 

explicit manner: producing copies, extracting elements, and eliminating irrelevant intersections.

The first step in the solution involves producing multiple copies of each source row—one for each 

column that you need to unpivot. In this case, you need to produce a copy for each of the columns 

A, B, C, and D, which represent customer IDs. In relational algebra and in SQL, the operation used to 

produce multiple copies of each row is a Cartesian product (a cross join). You need to apply a cross 

join between the EmpCustOrders table and a table that has a row for each customer. 

You can use a table value constructor in the form of a VALUES clause to create a virtual table with a 

row for each customer. The query implementing the first step in the solution looks like this.

SELECT * 

FROM dbo.EmpCustOrders 

  CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid);

Note that if you’re not familiar yet with the VALUES clause, it is described in detail in Chapter 8, 

“Data Modification.”

www.it-ebooks.info

---

230  

Microsoft SQL Server 2012 T-SQL Fundamentals

In this example, the query that implements the first step in the solution returns the following output.

empid       A           B           C           D           custid 

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

1           NULL        20          34          NULL        A 

1           NULL        20          34          NULL        B 

1           NULL        20          34          NULL        C 

1           NULL        20          34          NULL        D 

2           52          27          NULL        NULL        A 

2           52          27          NULL        NULL        B 

2           52          27          NULL        NULL        C 

2           52          27          NULL        NULL        D 

3           20          NULL        22          30          A 

3           20          NULL        22          30          B 

3           20          NULL        22          30          C 

3           20          NULL        22          30          D

As you can see, four copies were produced for each source row—one each for customers A, B, C, 

and D.

The second step in the solution is to produce a column (call it qty in this case) that returns the 

value from the column that corresponds to the customer represented by the current copy. More 

specifically in this case, if the current custid value is A, the qty column should return the value from 

column A, if custid is B, qty should return the value from column B, and so on. You can implement this 

step with a simple CASE expression like this.

SELECT empid, custid, 

  CASE custid 

    WHEN 'A' THEN A 

    WHEN 'B' THEN B 

    WHEN 'C' THEN C 

    WHEN 'D' THEN D     

  END AS qty 

FROM dbo.EmpCustOrders 

  CROSS JOIN (VALUES('A'),('B'),('C'),('D')) AS Custs(custid);

This query returns the following output.

empid       custid    qty 

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

1           A         NULL 

1           B         20 

1           C         34 

1           D         NULL 

2           A         52 

2           B         27 

2           C         NULL 

2           D         NULL 

3           A         20 

3           B         NULL 

3           C         22 

3           D         30

www.it-ebooks.info

---