Sql server® 2012 t-sql fundamentals

Solutions

This section provides solutions to the exercises for this chapter.

1-1

Producing multiple copies of rows can be achieved with a fundamental technique that utilizes a cross 

join. If you need to produce five copies of each employee row, you need to perform a cross join be-

tween the Employees table and a table that has five rows; alternatively, you can perform a cross join 

between Employees and a table that has more than five rows, but filter only five from that table in the 

WHERE clause. The Nums table is very convenient for this purpose. Simply cross Employees and Nums, 

and filter from Nums as many rows as the number of requested copies (five, in this case). Here’s the 

solution query.

SELECT E.empid, E.firstname, E.lastname, N.n 

FROM HR.Employees AS E 

  CROSS JOIN dbo.Nums AS N 

WHERE N.n <= 5 

ORDER BY n, empid;

www.it-ebooks.info

---