如何在SQL表中生成行以将范围分成单个行 [英] How can I generate rows in a SQL table to break up a range into induvidual rows

问题描述

我的数据具有类似于以下内容的信息:

I have data with info similar to the below:

Customer  Start   End
AAA        100    399 
BBB        400    899
CCC        900    999  
AAA        1000   1199

我需要将其变成2列，其中一列带有客户名称，另一列具有范围内的每个值. I.E.

What I need it to become is 2 Columns, 1 with customer name and the other with each value within the range. I.E.

Customer    Number
AAA           100
AAA           101
AAA           102   ETC

如何用SQL编写?

推荐答案

如果您已经有一个数字表，请使用它.如果没有，您可以使用cte即时生成一个数字表.

If you already have a numbers table, use it. If not, you can generate a numbers table on the fly using a cte.

首先，创建并填充示例表(请在您将来的问题中为我们保存此步骤):

First, create and populate sample table(Please save us this step in your future questions):

DECLARE @T AS TABLE
(
    Customer char(3),
    Start int,
    [End] int
);

INSERT INTO @T(Customer, Start, [End]) VALUES
('AAA', 100, 399), 
('BBB', 400, 899),
('CCC', 900, 999),  
('AAA', 1000, 1199);

然后，使用cte生成所需的电话号码

Then, use a cte to generate the numbers you need

WITH Tally(n) AS
(
    SELECT TOP(select max([End]) from @T) ROW_NUMBER() OVER(ORDER BY @@SPID) 
    FROM sys.objects
)

最后，从连接到cte的表中选择:

Finally, select from the table joined to the cte:

SELECT Customer, n
FROM @T
JOIN Tally 
    ON n >= Start
    AND n <= [End]
ORDER BY Customer, n    

这篇关于如何在SQL表中生成行以将范围分成单个行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！