8

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.

Art
  • 5,616
  • 1
  • 20
  • 22
  • Is there a max number of numbers you need? – sgeddes Feb 22 '13 at 19:37
  • @sgeddes - no, in Oracle there could be any number instead of 10 as in my example. I limited my example to 10 for simplicity. Thank you. – Art Feb 22 '13 at 19:38
  • 1
    Without having an oracle instance to play around with, I had to read some documentation. I think recursive CTEs are as close as you're going to get in T-SQL. – Ben Thul Feb 22 '13 at 20:37
  • WITH dynamicRecords(myDate, level) AS ( SELECT GETDATE() AS myDate, 1 AS level UNION ALL SELECT myDate + 1, level + 1 /* 12 Days - WHERE level < 12 */ --SELECT DATEADD(month, -1, myDate), level + 1 /* 12 Months - WHERE level < 12 */ FROM dynamicRecords WHERE level < 12 ) SELECT * FROM dynamicRecords Option (MaxRecursion 0) – Code Novice May 12 '22 at 19:19
  • https://stackoverflow.com/questions/7013348/possible-recursive-cte-query-using-date-ranges/72221201#72221201 – Code Novice May 12 '22 at 19:33

1 Answers1

15

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

sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • @sgeddes- Thank you. Still looking for equiv. to LEVEL pseudocolumn. I was hoping for examples with Hierarchy methods also. – Art Feb 22 '13 at 20:10
  • @Art - There is no equivalent exposed to us of the level column (though SQL Server keeps track of it internally to see if the max recursion limit is reached) but it is trivial to add a level column yourself as in [the example here](http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx) – Martin Smith Feb 23 '13 at 11:25
  • Strange this question is closed... I have a perfect DATE RANGE example using LEVEL syntax as an equivalent to Oracle however the link stating this is a DUPLICATE takes you to answers that have NOTHING to do with DATES but Numbers. Working with plain numbers is easy... Dates take a bit more finesse. Unfortunate I can't update this as each time I googled Date Range CTE this answer comes up and then I'm directed over to NUMBER CTEs. – Code Novice May 12 '22 at 19:18
  • WITH dynamicRecords(myDate, level) AS ( SELECT GETDATE() AS myDate, 1 AS level UNION ALL SELECT myDate + 1, level + 1 /* 12 Days - WHERE level < 12 */ --SELECT DATEADD(month, -1, myDate), level + 1 /* 12 Months - WHERE level < 12 */ FROM dynamicRecords WHERE level < 12 ) SELECT * FROM dynamicRecords Option (MaxRecursion 0) – Code Novice May 12 '22 at 19:18
  • https://stackoverflow.com/questions/7013348/possible-recursive-cte-query-using-date-ranges/72221201#72221201 – Code Novice May 12 '22 at 19:33