Thursday, September 13, 2007

Create a Number Table [sql] [numbers] [udf] [monotonous]

When you're doing crazy things with data generation or math, you need a numbers table or function in Sql. 

This query generates a list of 10 new investment employees per department in the department table in Sql 2005, using the numbers function in the link.

select row_number() OVER ( ORDER BY Num.Number) +1000 as rownum, 
rtrim(ltrim(Dept.[Department Name])) + ' - Investment Employee ' + Convert(varchar(2),Num.Number) as EmployeeName
from dbo.NumberTable (1,10) Num
cross join d_department Dept
order by EmployeeName

Create a Number Table [sql] [numbers] [udf] [monotonous]

No comments: