Click here to Skip to main content
14,427,367 members
Rate this:
Please Sign up or sign in to vote.
See more:
i have multiple rows with columns in Table, i want to add every 10 rows one groupid.

For example - first 10 rows groupid=1,next 10 rows groupid 2 ,next 10 rows groupid=3 etc.

Please help me out this problem ASAP.
Thanks in Advance.

What I have tried:

i dont know what i need to do.
Posted
Updated 9-Jan-20 6:05am
v2
Comments
ZurdoDev 9-Jan-20 7:44am
   
I believe ROWNUMBER and partition will help you out.
Afzaal Ahmad Zeeshan 9-Jan-20 8:03am
   
Do you want to do that in the physical data or in a view or something?
Richard MacCutchan 9-Jan-20 9:43am
   
Please do not use "ASAP" or "urgent" in your questions. People here answer questions in their own time and at no cost to you.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Look for ranking functions of SQL...
This one probably will do: NTILE (Transact-SQL) - SQL Server | Microsoft Docs[^]
   
Comments
0x01AA 9-Jan-20 11:04am
   
+5Nice function, but unfortunately the first group will be adjusted in case "no of rows % N" is not equal to zero.
Kornfeld Eliyahu Peter 12-Jan-20 6:01am
   
Yeas. I wasn't sure about NTILE... it was a suggestion from the top of my head... What I was sure is the ranking functions... :-)
Rate this:
Please Sign up or sign in to vote.

Solution 2

While NTILE mentioned in Answer 1has some disadvantages I would try something like this:

SELECT 
(ROW_NUMBER() OVER (ORDER BY Id)-1) / 10 + 1 AS GroupId
      ,[Id]
      , ....
FROM TBL
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100