Groups sequence in SQL Query

Question

Hi All,

I have a requirement, below is the output for source table.

---

DECLARE @Table1 TABLE (SNO int)
DECLARE @N int = 4;

INSERT INTO @Table1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)

Is there any gap in the values , output would be like this

Thanks in Advance..

Answer 1

Select SNO, 
  SNO%@N + Case When SNO%@N = 0 Then @N Else 0 End As GRPSEQ, 
  (SNO+@N-1)/@N AS [GROUP]
From @Table1

Tom

Answer 2

Hi @BHVS
Please also check this:

SELECT SNO,  
       CASE WHEN SNO%@N =0 THEN @N ELSE SNO%@N END AS [GRPSEQ],  
	   CEILING(SNO*1.0/@N) AS [GROUP]  
FROM @Table1  

Here I used CEILING function to return the smallest integer greater than, or equal to, the specified numeric expression.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.