Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Unanswered: Expanding a number range

    I have a table (we'll call it Ranges) of number ranges:

    ID Start End
    1 12345 12347
    2 12355 12359
    3 12380 12389
    4...

    Note: Start and End are text values

    What I need is to output these ranges with their corresponding ID:

    ID Number
    1 12345
    1 12346
    1 12347
    2 12355
    2 12356
    2...

    Note: Number needs to be text

    Can anyone help?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What's the maximum range between start and end?
    You'll need a table of sequential numbers with at least that many entries, and then the sql is pretty trivial.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2012
    Posts
    213
    Try something like that:

    Code:
    with CTE as
    (
        select 
            [ID], 
            CAST([Start] as int) as StartInt, 
            CAST([End] as int) as EndInt
        from @Ranges
        
        union all
        
        select 
            ID,
            StartInt + 1,
            EndInt
        from CTE
        where StartInt < EndInt
    )
    
    select ID, CAST(StartInt as varchar) as Number
    from CTE
    order by ID, StartInt
    Hope this helps.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •