SQL Server equivalent to Oracle CONNECT BY and LEVEL pseudocolumn

oracle, sql, sql-server

Solution

One way I've done it in the past is querying `spt_values` like this:

SELECT number
FROM master..spt_values
WHERE 
    type = 'P'
    AND number <= 255

However, it doesn't have a full list of numbers. An alternative option would be to create a Recursive CTE like such:

WITH CTE AS (
  SELECT 1 as Number
  UNION ALL
  SELECT Number+1
  FROM CTE 
  WHERE Number < 100 
)
SELECT * FROM CTE

SQL Fiddle Demo

Problem

Please help converting Oracle query to SQL Server equivalent: ``` SELECT (LEVEL+1-1) AS lvl FROM dual CONNECT BY LEVEL <= 10 / ``` The output is numbers from 1 to 10: ``` LVL ---- 1 2 3 ... 10 ``` I know there is hierarchy methods in SQL Server and built-ins like `GetLevel` and more. Can this be used to get the same results? To create dual table if needed (not sure) - copied from here:http://blog.sqlauthority.com/2010/07/20/sql-server-select-from-dual-dual-equivalent/ ``` CREATE TABLE DUAL ( DUMMY VARCHAR(1) ) GO INSERT INTO DUAL (DUMMY) VALUES ('X') GO ``` Specifically looking for examples that would let use smth. like LEVEL in queries. For example: there is only one start date in the table - 4/22/2013. But with LEVEL I'm able to increment it as follows: ``` SELECT start_date, start_date+LEVEL-1 AS start_date_btwn FROM my_tab WHERE id = 1 CONNECT BY LEVEL<=10 / START_DATE START_DATE_BTWN ------------------------------ 4/22/2013 4/22/2013 4/22/2013 4/23/2013 4/22/2013 4/24/2013 4/22/2013 4/25/2013 ...... 4/22/2013 4/30/2013 ``` Thank you very much to all in advance.

Original source

Related problems