Cross Join Values in SQL Server 2005

Question

I'm using SQL Server 2005 and I get an error:

Incorrect syntax near the keyword 'VALUES'.

when trying to run this query:

  SELECT T.N 
  FROM Table
  CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9)) as T(N)
  WHERE 1 = 1

But not in SQL Server 2008 -- works great in 2008.

What do I have to do in SQL Server 2005 to get this working?

Answer 1

Just use select with union all instead:

SELECT T.N
FROM Table CROSS JOIN
     (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5 union all
      select 6 union all select 7 union all select 8 union all select 9
     ) as T(N)
WHERE 1=1;

Or, use a recursive CTE so you don't have to type in the values:

with t(n) as
      select 1 as n
      union all
      select n + 1
      from t
      where n < 9
     )
select t.n
from table1 cross join
     t
where 1 = 1;