## 5 replies

Last post Dec 13, 2016 11:09 AM by eralper

• Rameezwaheed

Contributor

2599 Points

1754 Posts

### How to create numbers of rows based on totalCount

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

life is name of learning!
Mark as an answer if it helps
• eralper

Contributor

6081 Points

1443 Posts

### Re: How to create numbers of rows based on totalCount

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,

• Rameezwaheed

Contributor

2599 Points

1754 Posts

### Re: How to create numbers of rows based on totalCount

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,

life is name of learning!
Mark as an answer if it helps
• eralper

Contributor

6081 Points

1443 Posts

### Re: How to create numbers of rows based on totalCount

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

• Rameezwaheed

Contributor

2599 Points

1754 Posts

### Re: How to create numbers of rows based on totalCount

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

life is name of learning!
Mark as an answer if it helps
• eralper

Contributor

6081 Points

1443 Posts

### Re: How to create numbers of rows based on totalCount

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), *