从SQL Server 2008 R2中的多个值(列)获取多个行

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-3的Mike值域)为:

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

非常感谢您的建议

3 个解决方案

#1

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

rextester演示: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 |
+----+------+-----------+

#2

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 |
+----+------+----+

#3

You could use recursive cte like this

你可以像这样使用递归cte

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

演示链接:http://rextester.com/AFNYFW81782

#1