2

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 in the following format

ColA|ColB|RangeNumber
----------------------
  1   A       1
  1   A       2 
  1   A       3
  1   A       4
  1   A       5

I'm not familiar enough with views so I need some direction.

Thanks

Tim
  • 1,249
  • 5
  • 28
  • 54

2 Answers2

5

You can accomplish this using a recursive CTE

CREATE TABLE ranges (
    ColA int,
    ColB char,
    LowRange int,
    HighRange int,
);

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

CREATE VIEW range_view
AS
WITH each AS
(
    SELECT ColA, ColB, LowRange AS n, HighRange
      FROM ranges
    UNION ALL
    SELECT ColA, ColB, n + 1, HighRange
      FROM each
     WHERE n + 1 <= HighRange
)
SELECT ColA, ColB, n
FROM each
GO

SELECT * FROM range_view
DROP VIEW range_view
DROP TABLE ranges;
axblount
  • 2,639
  • 23
  • 27
  • excellent solution. I was headed down that path but it would have taken me much longer to get there. I implemented it on the live table and I encountered the "maximum recursion" error but I corrected that and it works like a charm. Thanks – Tim Oct 25 '13 at 21:30
  • @Tim - Joining onto a permanent auxiliary numbers table [will likely perform better though](http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232) – Martin Smith Oct 25 '13 at 23:44
1

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.

MJSalinas
  • 139
  • 1
  • 9