Creating rows with an index between two numbers

Question

I have one 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 that will generate a list of these values:

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

Edit

Although MinSP and MaxSP never differ from each other in my original example, it is possible that they will both be the same or shared by more than one. I changed the example to illustrate.

Answer 1

You will need a Tally table to do the following, but it beats the cursor and grows dynamically with the next OS released, your table of tables should also be zero.

EDIT: ​​ ​​

1 ( Tally): , sys.all_columns. , .

;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

( Tally, ):

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

Answer 2

:

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

ORDER BY .

. .

Answer 3

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 assumed that you will have a second table called SPNums that is filled with as many numbers as you need, starting at 0.

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

And then the request:

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