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

• 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 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 Thursday, October 7, 2021 12:00 AM
Tuesday, December 13, 2016 11:09 AM