Generate rows with index between two numbers

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

OS          MinSP      MaxSP
--          -----      -----
2000   开发者_Go百科     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.

---

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

---

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.

---

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