Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 231

Recall that in the original table, NULL marks represent irrelevant intersections. To eliminate irrele-

vant intersections, define a table expression based on the query that implements step 2 in the solution, 

and in the outer query, filter out NULL marks. Here’s the complete solution query.

SELECT * 

FROM (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)) AS D 

WHERE qty IS NOT NULL;

This query returns the following output.

empid       custid    qty 

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

1           B         20 

1           C         34 

2           A         52 

2           B         27 

3           A         20 

3           C         22 

3           D         30