How to query a variable number of rows from a values list?-CodePudding

My situation is that I need to produce a number of rows from a fixed list
The fixed list has 11 rows, and I need to select a variable number of rows from it

I can do it like this

select r.ritid, r.loadfactor, cvirtual.ProductSequence
from   rit r
outer apply ( select top 5 --r.loadfactor
                     RitID,
                     CarID,
                     row_number() over (partition by RitID order by RitID) as ProductSequence
              from ( values(r.RitID, 1),
                           (r.RitID, 2),
                           (r.RitID, 3),
                           (r.RitID, 4),
                           (r.RitID, 5),
                           (r.RitID, 6),
                           (r.RitID, 7),
                           (r.RitID, 8),
                           (r.RitID, 9),
                           (r.RitID, 10),
                           (r.RitID, 11)
                   ) as X(RitID, CarId)
              --where  c.CarID is null
            ) cvirtual --on r.RitID = cvirtual.RitID

This will get me 5 rows from the list,
but I don't want to write top 5 but something like top r.LoadFactor which is not possible off course.

So, how could I do this ?
Without using dynamic sql please

You can find a DBFiddle here

If in table rit the column LoadFactor is 3 then I want this result

ritid   loadfactor  ProductSequence
1       5           1
1       5           2
1       5           3

If in table rit the column LoadFactor is 5 then I want this result

ritid   loadfactor  ProductSequence
1       5           1
1       5           2
1       5           3
1       5           4
1       5           5

CodePudding user response：

As Jeroen Mostert said in the comments it is possible to use top with a column as you can see in this db<>fiddle but you need to add parenthesis around column name, example top(r.loadfactor).

So if r.loadfactor is 3 it will return only 3 rows, and if it's 5 it will return 5 rows. Just add order by to the top results.

Running your query with a update on loadfactor as shown here:

update rit
set loadfactor = 3

select r.ritid, r.loadfactor, cvirtual.ProductSequence
from   rit r
outer apply ( select top(r.loadfactor)
                     RitID,
                     CarID,
                     row_number() over (partition by RitID order by RitID) as ProductSequence
              from ( values(r.RitID, 1),
                           (r.RitID, 2),
                           (r.RitID, 3),
                           (r.RitID, 4),
                           (r.RitID, 5),
                           (r.RitID, 6),
                           (r.RitID, 7),
                           (r.RitID, 8),
                           (r.RitID, 9),
                           (r.RitID, 10),
                           (r.RitID, 11)
                   ) as X(RitID, CarId)
              --where  c.CarID is null
            ) cvirtual --on r.RitID = cvirtual.RitID

Returns:

 ------ ------------ ----------------- 
| ritid| loadfactor | ProductSequence |
 ------ ------------ ----------------- 
|1     | 3          | 1               |
|1     | 3          | 2               |
|1     | 3          | 3               |
 ------ ------------ -----------------