TSQL : Convert a Range to Rows in a View

You have a table that contains rows identifying start and end values for a range and you want to convert this to rows i.e. one row for every number between the start of the range and the end of the range. You also need to do it in a view.

An example is :

Id StartRange EndRange
1 101 105

Which you want represented as :

Id StartRange EndRange RangeValue
1 101 105 101
1 101 105 102
1 101 105 103
1 101 105 104
1 101 105 105

Moving along, your view would look something like this, a Common Table Expression embedded in a view…

CREATE VIEW [dbo].[RangeBuster_VW]
AS
WITH RangeBuster (Id, SomeDate, StartRange, EndRange, RangeValue)
 AS (
SELECT [Id]
      ,[SomeDate]
      ,CAST([StartRange] as int) AS StartRange
      ,CAST([EndRange] as int) AS EndRange
      ,CAST([StartRange] as int) AS RangeValue
  FROM [dbo].[SomeTable]
UNION ALL
SELECT [Id]
      ,[SomeDate]
      ,[StartRange]
	  ,[EndRange]
	  ,[RangeValue] + 1
FROM RangeBuster
WHERE [RangeValue] < [EndRange] )
SELECT * FROM RangeBuster
GO

So this is good but say you also want to rank the rows in each grouping :

Id StartRange EndRange RangeValue Rank
1 101 105 101 5
1 101 105 102 4
1 101 105 103 3
1 101 105 104 2
1 101 105 105 1

…then add the following to your SELECT clause within the view…

SELECT *, row_number() over (
         partition by Id
		 order by RangeValue desc) as [Rank]
FROM RangeBuster

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s