Geek asks and answers

SQL Server is equivalent to the alias Oracle CONNECT BY and LEVEL

Please help convert an Oracle query to the equivalent of SQL Server:

SELECT (LEVEL+1-1) AS lvl 
  FROM dual
CONNECT BY LEVEL <= 10
/

The conclusion is numbers from 1 to 10:

LVL
----
1
2
3
...
10

I know that there are hierarchy methods and built-in functions in SQL Server, such as GetLeveland much more. Can this be used to obtain the same results?

To create a double table, if necessary (not sure), copied 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

In particular, looking for examples that would allow smth to be used. like LEVEL in queries. For example: there is only one start date in the table - 4/22/2013. But with LEVEL, I can increase 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 all in advance.

+5
1

, , - spt_values :

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

. CTE, :

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

- SQL Fiddle

+9

More articles:


All Articles