locked
How to create numbers of rows based on totalCount RRS feed

  • 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

  • User-595703101 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 + ((rn-1) / @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

  • User-595703101 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 + ((i-1) / @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
  • User-595703101 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
  • User-595703101 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 + ((rn-1) / @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