My question is:
I have a table containing 25 records of people names. But I need to randomly divide it into 5 groups of 5 people.
Being a 'Group A' table with 5 people, another table 'Group B' with 5 people and so on.
You need to be in SQL Server because the mass of data is much larger than this. Does anyone know how to use the rand() function?
I put it like this:
select * from pessoas order by RAND()
But how do I put, for example, to separate into groups of 5?
I think that's what you expect.
DECLARE @nQtdeGrupo INT = 5
SELECT cNome
, iGrupo
FROM (SELECT cNome = PESSOAS.NOME
, iGrupo = (ROW_NUMBER() OVER(ORDER BY NEWID() DESC) % @nQtdeGrupo) + 1
FROM PESSOAS
) tGrupo
ORDER BY iGrupo
Using your logic you get the rest of the division by 5 that will be between a number between 0 and 5.
SELECT *, FLOOR((RAND() * 1481) % 5) AS grupo FROM pessoas ORDER BY grupo
Because the RAND function returns a number between 0 and 1 it is necessary to multiply to get a large integer.
You can do the division using the NTILE function, example:
DECLARE @Count int
,@MaxRowsPerTable int = 5
SELECT @Count = COUNT() FROM dbo.Pessoas
/* Número máximo de tabelas que serão criadas */
SET @MaxCreationTable = FLOOR(@Count / @MaxRowsPerTable)
SELECT NTILE(@MaxCreationTable) OVER (ORDER BY Nome) 'Grupo', Nome FROM Pessoas
I mention how many rows are per table, then I calculate the maximum number of tables that will be created and set this value within the NTILE parameter. This allows you to create almost perfect balancing (perfect only if the number of rows is divisible by the total number of rows in a table.
From here, you can create a WHILE responsible for creating pivot tables based on count and enter values where COUNT is equal to NTILE
See if this meets your need.