SQL SELECT Convert Min/Max into Separate Rows

Question

I have a table that has a min and max value that I'd like create a row for each valid number in a SELECT statement.

Original table:

| Foobar_ID | Min_Period | Max_Period |
---------------------------------------
| 1         | 0          | 2          |
| 2         | 1          | 4          |

I'd like to turn that into:

| Foobar_ID | Period_Num |
--------------------------
| 1         | 0          |
| 1         | 1          |
| 1         | 2          |
| 2         | 1          |
| 2         | 2          |
| 2         | 3          |
| 2         | 4          |

The SELECT results need to come out as one result-set, so I'm not sure if a WHILE loop would work in my case.

Join to a table with numbers in it: `ON n between Min_Period and Max_Period` — dnoeth, Apr 04 '18 at 15:25
Possible duplicate of [Create a sql view based converting ranges into rows](https://stackoverflow.com/questions/19598409/create-a-sql-view-based-converting-ranges-into-rows) — Tab Alleman, Apr 04 '18 at 15:40

Gordon Linoff · Accepted Answer · 2018-04-04T15:44:47.367

2

If you expect just a handful of rows per foobar, then this is a good opportunity to learn about recursive CTEs:

with cte as (
      select foobar_id, min_period as period_num, max_period
      from original t
      union all
      select foobar_id, min_period + 1 as period_num, max_period
      from cte
      where period_num < max_period
    )
select foobar_id, period_num
from cte
order by foobar_id, period_num;

You can extend this to any number of periods by setting the MAXRECURSION option to 0.

edited Apr 04 '18 at 15:44

answered Apr 04 '18 at 15:25

Gordon Linoff

1,242,037
58
646
786

1

Can you edit the table's names? The CTE name is `t` but you are selecting from `cte`. Would be great if you could also add the `MAXRECURSION` option so the OP knows where to edit. – EzLo Apr 04 '18 at 15:30
My range of periods is 0 to 11 so I suppose just repeating the `SELECT` statement would work, but I was hoping for a more dynamic solution. I'll work on this approach for now. Thx – Panman Apr 04 '18 at 15:31
1

This answer is the one you need @Panman, it's a recursive CTE that will generate all records in one go. – EzLo Apr 04 '18 at 15:31
Yes, recursive CTE works great. After reviewing the this suggestion more closely I got it to work as intended. Thx again! – Panman Apr 04 '18 at 16:02

Thom A · Answer 2 · 2018-04-04T15:38:34.310

One method would be to use a Tally table, ther's plenty of examples out there, but I'm going to create a very small one in this example. Then you can JOIN onto that and return your result set.

--Create the Tally Table
CREATE TABLE #Tally (I int);

WITH ints AS(
    SELECT 0 AS i
    UNION ALL
    SELECT i + 1
    FROM ints
    WHERE i + 1 <= 10)
--And in the numbers go!
INSERT INTO #Tally
SELECT i
FROM ints;
GO

--Create the sample table
CREATE TABLE #Sample (ID int IDENTITY(1,1),
                      MinP int,
                      MaxP int);

--Sample data    
INSERT INTO #Sample (Minp, MaxP)
VALUES (0,2),
       (1,4);
GO

--And the solution
SELECT S.ID,
       T.I AS P
FROM #Sample S
     JOIN #Tally T ON T.I BETWEEN S.MinP AND S.MaxP
ORDER BY S.ID, T.I;
GO

--Clean up    
DROP TABLE #Sample;
DROP TABLE #Tally;

Thanks for the suggestion, after reviewing "recursive CTE's" I was able to do everything in one CTE. — Panman, Apr 04 '18 at 16:01

score 0 · Answer 3 · answered Apr 04 '18 at 15:59

Depending on the size of the data and the range of the period, the easiest way to do this is to use a dynamic number fact table, as follows:

WITH rn AS (SELECT ROW_NUMBER() OVER (ORDER BY object_id) -1 as period_num FROM sys.objects)
SELECT f.foobar_id, rn.period_num
FROM foobar f
    INNER JOIN rn ON rn.period_num BETWEEN f.min_period AND f.max_period

However, if you're working with a larger volume of data, it will be worth creating a number fact table with an index. You can even use a TVV for this:

-- Declare the number fact table
DECLARE @rn TABLE (period_num INT IDENTITY(0, 1) primary key, dummy int)
-- Populate the fact table so that all periods are covered
WHILE (SELECT COUNT(1) FROM @rn) < (SELECT MAX(max_period) FROM foobar)
    INSERT @rn select 1 from sys.objects

-- Select using a join to the fact table
SELECT f.foo_id, rn.period_num
FROM foobar f
    inner join @rn rn on rn.period_num between f.min_period and f.max_period

score 0 · Answer 4 · answered Apr 10 '18 at 12:54

Just Create a function sample date and use it

CREATE FUNCTION [dbo].[Ufn_GetMInToMaxVal] (@Min_Period INT,@Max_Period INT  )
RETURNS  @OutTable TABLE
(
DATA INT
)
AS
BEGIN

;WIth cte
AS
(
SELECT @Min_Period As Min_Period
UNION ALL
SELECT Min_Period+1 FRom
cte 
WHERE Min_Period <  @Max_Period
)
INSERT INTO @OutTable
SELECT * FROM cte
RETURN
END

Get the result by executing sql statement

DECLARE @Temp AS TABLE(
        Foobar_ID INT,
        Min_Period INT,
        Max_Period INT
        )
INSERT INTO @Temp

SELECT  1, 0,2 UNION ALL
SELECT  2, 1,4

SELECT Foobar_ID ,
        DATA 
FROM @Temp
CROSS APPLY 
 [dbo].[Ufn_GetMInToMaxVal] (Min_Period,Max_Period)

Result

 Foobar_ID  DATA
 ----------------
    1        0
    1        1
    1        2
    2        1
    2        2
    2        3
    2        4

SQL SELECT Convert Min/Max into Separate Rows

4 Answers4