在SQL Server中获得来自多个值（列）多行2008 R2

问题描述：

我有这个表在SQL Server中获得来自多个值（列）多行2008 R2

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

我想基于范围（开始/结束）每个人的所有行。

结果应该是这个

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

系统可根据开始和结束列独特的价值观，我有这个功能

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) 
);

以上函数返回的输出（根据Mike的1-3范围）：

1 
2 
3

我一直在尝试几种方法，我找不到合适的解决方案，这似乎是一个非常普遍的任务，但却是一个棘手的问题。

任何输入使用cross apply()高度赞赏

答

：

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

rextester演示：http://rextester.com/FVA48693

回报：

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

它的工作完美！ :) – Carlos

@Carlos高兴地帮忙！ – SqlZim

答

可以使用如下帐簿表：

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

输出：

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

答

您可以使用recursive 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)

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

在SQL Server中获得来自多个值（列）多行2008 R2

相关推荐