I have a list of person by nationality. These people must be grouped or divided if their number is greater or less than 4 in group of max 4 person with same nationality.

The result that I wont is :

The goal is to get groups of 4 or less. For example if I have 41 rows of Swedish with Number=1 the goal is 10 rows of Swedish with GroupOf = 4 plus one row with groupOf = 1.

CodePudding user response：

You need to multiple and number the rows (using a tally/numbers table) and use an additional calculation (with the appropriate grouping):

Sample data and a tally table:

SELECT *
INTO Data
FROM (VALUES
   ('Italian', 7),
   ('Franch',  2),
   ('Franch',  3),
   ('English', 2),
   ('English', 1),
   ('Spanish', 9)
) v (Nationality, [Number])
SELECT *
INTO Tally
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) t (Rn)

Statement:

SELECT Nationality, COUNT(*) AS GroupOf
FROM (
   SELECT Nationality, ROW_NUMBER() OVER (PARTITION BY d.Nationality ORDER BY Nationality) AS Rn
   FROM Data d
   JOIN Tally t ON t.Rn <= d.[Number]
) t
GROUP BY Nationality, ((Rn - 1) / 4)

Result:

CodePudding user response：

Another option utilizing a tally/numbers table, is to group first, then explode the rows by joining the tally

SELECT
  d.Nationality,
  CASE WHEN t.Rn <= (d.GroupOf / 4)
    THEN 4
    ELSE d.GroupOf - ((t.Rn - 1) * 4)
  END AS GroupOf
FROM (
   SELECT Nationality, SUM(d.Number) AS GroupOf
   FROM Data d
   GROUP BY Nationality
) d
JOIN Tally t ON t.Rn <= ((d.GroupOf   3) / 4);

db<>fiddle