We have a SQL table with few columns and one of the columns is Item. It has various values shown below. Item Item1 Item2 Item3 Item4 Item5 Item6 Item7 Item8 Item9 Item10 Item11 Item12 I ...

SQL table : update column with batchName based on the total count of rows

We have a SQL table with few columns and one of the columns is Item. It has various values shown below.

Item
Item1
Item2
Item3
Item4
Item5
Item6
Item7
Item8
Item9
Item10
Item11
Item12

I need to divide this table into various sets based on the total number of rows in the table. The maximum sets allowed vary, but for example, here I use it as 6.

So, if there 12 rows in the table, I need to assign batch1 to the first 2 rows, batch2 to 3rd/4th row, and so on. if there are 14 rows, it is fine for the last 4 rows to have batch6 (because 14 / 6=approx 2 rows per batch)

What I tried so far is to use row_number over Item such that I get a unique list of rows (Item will be unique in this table and in this example it will be 1 to 12). Then write cursor-based logic to loop through the count and based on the factor of 6, set the value for BatchName.

Desired output is

Item	BatchName
Item1	Batch1
Item2	Batch1
Item3	Batch2
Item4	Batch2
Item5	Batch3
Item6	Batch3
Item7	Batch4
Item8	Batch4
Item9	Batch5
Item10	Batch5
Item11	Batch6
Item12	Batch6

What are the best options here? Any suggestions to produce this type of output?

Many thanks.

Answer

Just use arithmetic. In SQL Server, you can use:

select t.*,
       ( (row_number() over (order by item) + 1) / 2 ) as batch_number
from t

Advertisement

Answer