Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 245

4

This exercise involves a request to unpivot the source columns cnt2007, cnt2008, and cnt2009 to two 

target columns—orderyear to hold the year that the source column name represents and numorders 

to hold the source column value. You can use the solutions that I showed in the chapter as the basis 

for solving this exercise with a couple of small revisions.

In the examples I used in the chapter, NULL marks in the table represented irrelevant column 

values. The unpivoting solutions I presented filtered out rows with NULL marks. The EmpYearOrders 

table has no NULL marks, but it does have zeros in some cases, and the request is to filter out rows 

with 0 number of orders. With the standard solution, simply use the predicate numorders <> 0 in-

stead of using IS NOT NULL. Here’s the version that uses the VALUES clause.

SELECT * 

FROM (SELECT empid, orderyear, 

        CASE orderyear 

          WHEN 2007 THEN cnt2007 

          WHEN 2008 THEN cnt2008 

          WHEN 2009 THEN cnt2009 

        END AS numorders 

      FROM dbo.EmpYearOrders 

        CROSS JOIN (VALUES(2007),(2008),(2009)) AS Years (orderyear)) AS D 

WHERE numorders <> 0;

As for the solution that uses the native UNPIVOT operator, remember that it eliminates NULL 

marks as an integral part of its logic. However, it does not eliminate zeros—you have to take care of 

eliminating zeros yourself by adding a WHERE clause, like this.

SELECT empid, CAST(RIGHT(orderyear, 4) AS INT) AS orderyear, numorders 

FROM dbo.EmpYearOrders 

  UNPIVOT(numorders FOR orderyear IN(cnt2007, cnt2008, cnt2009)) AS U 

WHERE numorders <> 0;

Notice the expression used in the SELECT list to produce the orderyear result column: 

CAST(RIGHT(orderyear, 4) AS INT). The original column names that the query unpivots are 

cnt2007, cnt2008, and cnt2009. These column names become the values ‘cnt2007’, ‘cnt2008’, 

and ‘cnt2009’, respectively, in the orderyear column in the result of the UNPIVOT operator. The 

purpose of this expression is to extract the four rightmost characters representing the order year 

and convert the value to an integer. This manipulation was not required in the standard solution 

because the constants used to construct the table expression Years were specified as the integer 

order years to begin with.

www.it-ebooks.info

---