Get Multiple rows from multiple values (columns) in SQL Server 2008 R2

Question

I have this table

+-----+------+--------+--------+
| ID  | Name |  Start |   End  |
+-----+------+--------+--------+
| 20  | Mike |   1    |    3   |
| 21  | Luke |   4    |    7   |
+-----+------+--------+--------+

And I want to generate all rows based on the range (start / end) of each person.

The outcome should be this

+-----+------+-----------------+
| ID  | Name |    Start_End    |
+-----+------+-----------------+
| 20  | Mike |        1        |
| 20  | Mike |        2        |
| 20  | Mike |        3        |
| 21  | Luke |        4        |
| 21  | Luke |        5        |
| 21  | Luke |        6        |
| 21  | Luke |        7        |
+-----+------+--------+--------+

To get unique values based on Start and End column, I have this function

CREATE FUNCTION [dbo].[ufn_SplitRange] (@Start INT, @End INT)  
RETURNS TABLE  
AS  
RETURN   
(  
    SELECT  TOP (@End - @Start+1) ROW_NUMBER() OVER (ORDER BY S.[object_id])+(@Start - 1) [Start_End]
    FROM    sys.all_objects S WITH (NOLOCK)
);

The above function returns the output of (based on Mike range of 1-3):

1
2
3

I have been trying several approaches and, I can't find the right solution, it seems a very common task, but a tricky one.

Any input is highly appreciated

SqlZim · Accepted Answer · 2017-07-06 16:02:49Z

2

using cross apply():

select t.Id, t.Name, x.Start_End
from t
  cross apply dbo.ufn_SplitRange(t.Start,t.[End]) as x

rextester demo: http://rextester.com/FVA48693

returns:

+----+------+-----------+
| Id | Name | Start_End |
+----+------+-----------+
| 20 | Mike |         1 |
| 20 | Mike |         2 |
| 20 | Mike |         3 |
| 21 | Luke |         4 |
| 21 | Luke |         5 |
| 21 | Luke |         6 |
| 21 | Luke |         7 |
+----+------+-----------+

answered Jul 6, 2017 at 16:02

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kannan Kandasamy · Accepted Answer · 2017-07-06 16:03:00Z

1

You can use tally table as below:

Select Id, Name, Start_end from #Values
cross apply (
        Select top ([end] - [start] +1) Start_end = [start] + Row_number() over (order by (Select NULL))-1
        from master..spt_values s1, master..spt_values s2
        ) a

Output :

+----+------+----+
| Id | Name | RN |
+----+------+----+
| 20 | Mike |  1 |
| 20 | Mike |  2 |
| 20 | Mike |  3 |
| 21 | Luke |  4 |
| 21 | Luke |  5 |
| 21 | Luke |  6 |
| 21 | Luke |  7 |
+----+------+----+

answered Jul 6, 2017 at 16:03

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

Comments

TriV · Accepted Answer · 2017-07-06 16:06:49Z

0

You could use recursive cte like this

DECLARE @SampleData AS TABLE 
(
    Id int,
    Name varchar(10),
    Start int,
    [End] int
)

INSERT INTO @SampleData
(
    Id,
    Name,
    Start,
    [End]
)
VALUES
(1,'Mike',1,3),
(2,'Luke',4,7)

;WITH temp AS
(
    SELECT Id, sd.Name, sd.Start , sd.[End]
    FROM @SampleData sd

    UNION ALL

    SELECT t.Id, t.Name, t.Start + 1, t.[End]
    FROM temp t
    WHERE t.Start < t.[End]
)
SELECT t.Id, t.Name, t.Start AS [Start_End]
FROM temp t
ORDER BY t.Id
OPTION (MAXRECURSION 0)

Demo link: http://rextester.com/AFNYFW81782

answered Jul 6, 2017 at 16:06

TriV

5,1582 gold badges12 silver badges18 bronze badges

Collectives™ on Stack Overflow

Get Multiple rows from multiple values (columns) in SQL Server 2008 R2

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related