Generate rows with index between two numbers

Question

I have a single table varchar, int, int like this:

OS          MinSP      MaxSP
--          -----      -----
2000        4          4
XP          2          3
Vista       0          2
7           0          1

What I want is a query which will generate a list of values like this:

• 2000 SP4
• XP SP2
• XP SP3
• Vista
• Vista SP1
• Vista SP2
• 7
• 7 SP1

Edit

Although MinSP and MaxSP a never more than one apart in my original example, it's possible that they will both be the same or separated by more than one. I've changed the example to illustrate.

Answer 1

Quick answer:

Select OS + ' SP' + Convert(varchar(50),MinSp) as col1 from TABLE
UNION 
Select OS + ' SP' + Convert(varchar(50),MaxSp) as col1 from TABLE

Add ORDER BY as desired.

But see my comment to your question as well.

Answer 2

SELECT CASE WHEN MinSP = '0' THEN OS ELSE OS + ' SP' + cast(MinSP as 
    nvarchar(10)) END AS Results, MaxSP
FROM OS

UNION

SELECT CASE WHEN MaxSP = '0' THEN OS ELSE OS + ' SP' + cast(MaxSP as 
    nvarchar(10)) END AS Results, MaxSP
FROM OS
ORDER BY MaxSP DESC

EDIT:

And with your new criteria, I've assumed you'll have a second table called SPNums, which is filled with as many numbers as you think you'll need, starting with 0.

SPNum
-----
   0
   1 
   2
   3
   4
   5
   6

And then the query:

SELECT CASE WHEN SPNum = '0' THEN OS ELSE OS + ' SP' + cast(SPNum as 
    nvarchar(10)) END AS Results
FROM OS
LEFT OUTER JOIN SPNums ON SPNum >= MinSP AND SPNum <= MaxSP
ORDER BY OS

Answer 3

You would need a Tally table to do the following, but it beats a cursor and will grow dynamically with the next OS that is released. Your tally table will have to be zero based too.

EDIT: Fixed a typo and added a second version

Version 1 (You have not got a Tally Table): This generates a numbers table on the fly using sys.all_columns. There are many ways of doing this, but you get the idea.

;WITH    Tally(N)
          AS (SELECT    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 --minus one to make this zero based
              FROM      sys.all_columns C)
    SELECT  OS + CASE WHEN N > 0 THEN ' SP' + CAST(B.N AS char(1))
                      ELSE ''
                 END
    FROM    dbo.Test A
    INNER JOIN Tally B ON B.N >= A.MinSp
                          AND B.N <= A.MaxSp

Version two (You have a Tally Table that is zero based):

SELECT  OS + CASE WHEN N > 0 THEN ' SP' + CAST(B.N AS char(1))
                  ELSE ''
             END
FROM    dbo.Test A
INNER JOIN dbo.Tally B ON B.N >= A.MinSp
                          AND B.N <= A.MaxSp