Answered by:
How to create numbers of rows based on totalCount
Question

User1623409651 posted
Hi,
I have to write query for seating arrangement based on total number of students . i have to make rows based on total number of students and each row must contains 70 students
Suppose Total Student 900 (Count in Admission Table)
Row1 Row2 Row3............................................. Rown
1 71
2 72


70
Thanks
Tuesday, December 13, 2016 5:47 AM
Answers

User595703101 posted
Hi Rameez,
Sorry I'm late since I missed your post
I assume that the Admission table is as follows
create table Admission ( id int identity(1,1), studentname varchar(100) )
Here is how you can implement the solution with your table in use as follows.
I used id column to order the admission table rows, you can use any column in fact. But if you use studentname, it will sort students by name resulting the students with 'A' will be placed in the first rows. The students whose names starting with 'Z' will be placed in the last rows :)
declare @RowCapacity int = 70 ;with cte as ( select rn = ROW_NUMBER() over (order by id), * from Admission ) select rn, RowNumber = 1 + ((rn1) / @RowCapacity), SeatNumber = case when rn % @RowCapacity = 0 then @RowCapacity else (rn % @RowCapacity) end, id, studentname from cte
Here I used SQL CTE expression, you can use a lot in your queries
I hope it helps,
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, December 13, 2016 11:09 AM
All replies

User595703101 posted
Hello Rameez,
In order to simulate 900 students, I used a SQL numbers table function. Maybe you don't need it on your database. I guess you have a table (Admission Table) which you can use by modifying below query
I used the mod function for remaining when the student order no is divided by capacity (70) and and division operation where I get the integer value
declare @TotalCount int = 900 declare @RowCapacity int = 70 select StudentNo = i, RowNumber = 1 + ((i1) / @RowCapacity), SeatNumber = case when i % @RowCapacity = 0 then @RowCapacity else (i % @RowCapacity) end from dbo.numberstable(1,@TotalCount,1)
Output is like below showing which row and seat number the student will be sitting
StudentNo RowNumber SeatNumber    1 1 1 2 1 2 3 1 3 4 1 4 5 1 5 6 1 6 7 1 7 8 1 8 9 1 9 10 1 10 11 1 11 12 1 12 13 1 13 ... ... 891 13 51 892 13 52 893 13 53 894 13 54 895 13 55 896 13 56 897 13 57 898 13 58 899 13 59 900 13 60
I hope it helps,
Tuesday, December 13, 2016 6:19 AM 
User1623409651 posted
Thanks eralper for sharing the idea of using mod function.
Please correct me i have to pass the Row Capacity dynamically in stored procedure and need to get the total count from Admission table. still may i need to use the numbersTable function ?
Thanking you,
Regards,
Tuesday, December 13, 2016 6:31 AM 
User595703101 posted
If you have names or numbers for students which are not in order or gaps, you need the row number.
You can use the row_number() function too, to get the "i" value which is used in calculations for each student data
Tuesday, December 13, 2016 6:43 AM 
User1623409651 posted
Thanks eralper,
I have the names and number in Admission Table . i just want to use the names and number from that table and just need to create the seating row dynamically based on total number of students.
Please share the row_number() function .
Thanking you
Tuesday, December 13, 2016 6:55 AM 
User595703101 posted
Hi Rameez,
Sorry I'm late since I missed your post
I assume that the Admission table is as follows
create table Admission ( id int identity(1,1), studentname varchar(100) )
Here is how you can implement the solution with your table in use as follows.
I used id column to order the admission table rows, you can use any column in fact. But if you use studentname, it will sort students by name resulting the students with 'A' will be placed in the first rows. The students whose names starting with 'Z' will be placed in the last rows :)
declare @RowCapacity int = 70 ;with cte as ( select rn = ROW_NUMBER() over (order by id), * from Admission ) select rn, RowNumber = 1 + ((rn1) / @RowCapacity), SeatNumber = case when rn % @RowCapacity = 0 then @RowCapacity else (rn % @RowCapacity) end, id, studentname from cte
Here I used SQL CTE expression, you can use a lot in your queries
I hope it helps,
 Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Tuesday, December 13, 2016 11:09 AM