Divide Ids into multiple groups based on a number in sql server-CodePudding

Hope all is well,

I'm trying to divide records of data to groups

Each group size has 4 records, and the last group maybe less than 4, with numbering rows in each group, and count Ids in each group

The final result should be like this

Id,GroupId,RowNum,IdCount

I tried something like this

declare @t int set @t = (select count(*) from mytable ) 
declare @s int set @s = 4

select t.Id
     ,@s over (order by t.Id asc) as GroupId 
     ,case when (row_number() over (order by t.Id asc ) %@s ) = 0 then 4 else (row_number() over (order by t.Id asc) %@s ) end  as RowNum
     ,(count Ids in each group) IdCount
from mytable t
group by t.Id
order by t.Id asc

CodePudding user response：

You may use DENSE_RANK analytic function divided by 4 to define the id groups as the following:

WITH IdGroups AS
(
  SELECT ID,
         CEILING(DENSE_RANK() OVER (ORDER BY ID) *1.0/4) GRP
  FROM table_name
)
SELECT ID, GRP AS GroupID, ROW_NUMBER() OVER (PARTITION BY GRP ORDER BY ID) AS ROWNUM, 
       COUNT(*) OVER (PARTITION BY GRP) AS IDCount
FROM IdGroups
ORDER BY ID

See a demo.