sql server - SQL copy data to new table adding sequences by data grouping

Question

I am stuck on SQL I need to write (and I'm a SQL novice).

I have a table that contains data with a structure like this:

Table: CURRENT_TABLE

item_id	col1	col2	other_col
100	A	B	C
200	D	E	F
200	G	H	I
300	J	K	L
200	M	N	O

question from:https://stackoverflow.com/questions/65904695/sql-copy-data-to-new-table-adding-sequences-by-data-grouping

Answer 1

You can use row_number():

select item_id,
       row_number() over (partition by item_id order by (select null)) - 1 as seq,
       col1, col2, other_col
from t;

You can use into to put the results in a new table.