Equally divide resultset into groups, with cursor or not?

Question

I'm building a race administration system, with drivers and race heats.

I need to divide, lets say, 13 drivers into groups of maximum 6 per group. It's not possible since the result will be 2.2 groups, wich is impossible, 3 groups is required. Smaller groups than 6 is allowed, so I decide to divide 13 by 3 to accomplish the follwing division:

Heat 1: 4 drivers
Heat 2: 4 drivers
Heat 3: 5 drivers (the remaining drivers, but no more than 6)

I have managed to divide the rows and rounding the results etc. to know that 3 groups is needed and no less than 4 drivers per group is allowed. The tricky part is how to loop through this and add the rest (5) in the last loop... I'm thinking of "SELECT TOP 4..." for the two first, and "SELECT TOP 100%..." for the remaining five drivers.

I know cursors, but i'm not an expert and I know how to create and execute a dynamic sql query.

How can this be done by using cursors and/or counters in SQL Server 2005?

Answer 1

Edit: Quassnoi's NTILE version is much nicer.

You should be able to do this using a while loop instead of a cursor.

Assume you create a table "Race" with columns DriverId and GroupNumber (HeatNumber). You say you know how to calculate how many groups and how many drivers to put in each group. So you can have a loop like this:

set @group = 1
while @group < @totalGroups
begin
    insert Race (DriverId, GroupNumber)
    select top (@driversPerGroup) d.DriverId, @group
    from Drivers d
    where not exists(select * from Race r where r.DriverId = d.DriverId)
    order by whatever

    set @group = @group + 1
end

--insert last group here
insert Race (DriverId, GroupNumber)
select d.DriverId, @totalGroups
from Drivers d
where not exists(select * from Race r where r.DriverId = d.DriverId)

Answer 2

SELECT  *,
        NTILE((SELECT CAST(CEILING(COUNT(*) / 6.00) AS INT) FROM drivers)) OVER (ORDER BY id) AS heat
FROM    drivers