Create A Sql View Based Converting Ranges Into Rows

I have a table structured like so ColA|ColB|LowRange|HighRange ---------------------------- 1 A 1 5 I would like to create a view that will make the data available

Solution 1:

You can accomplish this using a recursive CTE

CREATETABLE ranges (
    ColA int,
    ColB char,
    LowRange int,
    HighRange int,
);

INSERTINTO ranges
VALUES (1, 'A', 1, 5),
(2, 'B', 5, 10);
GO

CREATEVIEW range_view
ASWITHeachAS
(
    SELECT ColA, ColB, LowRange AS n, HighRange
      FROM ranges
    UNIONALLSELECT ColA, ColB, n +1, HighRange
      FROMeachWHERE n +1<= HighRange
)
SELECT ColA, ColB, n
FROMeach
GO

SELECT*FROM range_view
DROPVIEW range_view
DROPTABLE ranges;

Solution 2:

The only way I can figure this one out is by creating a separate table that has all the numbers and then join to the original table. I created a table called 'allnumbs' and it has only one column with the name of 'num' and a record for every number between 1 and 10. Then you join them.

select cola, colb, b.num from temp a
join allnumbs b on b.num >= a.lownum and b.num <= a.highnum

Table temp is your table that your displayed. Hope this helps.