Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 241

4

Run the following code to create and populate the EmpYearOrders table.

USE TSQL2012; 

 

IF OBJECT_ID('dbo.EmpYearOrders', 'U') IS NOT NULL DROP TABLE dbo.EmpYearOrders; 

 

CREATE TABLE dbo.EmpYearOrders 

( 

  empid INT NOT NULL 

    CONSTRAINT PK_EmpYearOrders PRIMARY KEY, 

  cnt2007 INT NULL, 

  cnt2008 INT NULL, 

  cnt2009 INT NULL 

); 

 

INSERT INTO dbo.EmpYearOrders(empid, cnt2007, cnt2008, cnt2009) 

  SELECT empid, [2007] AS cnt2007, [2008] AS cnt2008, [2009] AS cnt2009 

  FROM (SELECT empid, YEAR(orderdate) AS orderyear 

        FROM dbo.Orders) AS D 

    PIVOT(COUNT(orderyear) 

          FOR orderyear IN([2007], [2008], [2009])) AS P; 

 

SELECT * FROM dbo.EmpYearOrders;

Here is the output for the query.

empid       cnt2007     cnt2008     cnt2009 

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

1           1           1           1 

2           1           2           1 

3           2           0           2

Write a query against the EmpYearOrders table that unpivots the data, returning a row for each 

employee and order year with the number of orders. Exclude rows in which the number of orders is 0 

(in this example, employee 3 in the year 2008).

■

■

Desired output:

empid       orderyear   numorders 

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

1           2007        1 

1           2008        1 

1           2009        1 

2           2007        1 

2           2008        2 

2           2009        1 

3           2007        2 

3           2009        2

www.it-ebooks.info

---