The kind of data we have is
From To
1 5
6 10
Now, the result should be like-
Series
1
2
3
4
5
6
7
8
9
10
I have achieved this result using Cursor but just curious to know if this can be achieved by using any other method.
Add a comment
|
2 Answers
You can do this in two ways.
Use a Recursive CTE to generate Numbers between minimum and maximum value in your table
CREATE TABLE #test([From] INT,[To] INT)
INSERT #test
VALUES (1,5),(6,10)
DECLARE @max INT
SELECT @max = Max([To])
FROM #test;
WITH cte
AS (SELECT Min([From]) Series
FROM #test
UNION ALL
SELECT Series + 1
FROM cte
WHERE Series < @max)
SELECT Series
FROM cte a
JOIN #test b
ON a.Series BETWEEN b.[From] AND b.[To]
Or use Master..spt_values to get the numbers
SELECT number As Series
FROM master..spt_values a
JOIN #test b
ON a.number BETWEEN b.[From] AND b.[To]
WHERE type = 'p'
OUTPUT :
+------+
|Series|
+------+
|1 |
|2 |
|3 |
|4 |
|5 |
|6 |
|7 |
|8 |
|9 |
|10 |
+------+
CREATE TABLE #tempnumber (num INT)
INSERT INTO #tempnumber
SELECT t.[to] FROM yourtable t
UNION
SELECT t1.[from] FROM yourtable t1
DECLARE @max INT, @min INT
SELECT @max = MAX(num) FROM #tempnumber
SELECT @min = MIN(num) FROM #tempnumber
;WITH Series(a) AS
(
SELECT (SELECT @min)
UNION ALL
SELECT a+1 FROM Series WHERE a < (SELECT @max)
)
SELECT * FROM Series;
