SQL copy data to new table adding sequences by data grouping

user_4463156 0
Viewed 32

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

And the primary key is: item_id, col1 and col2.

But this PK structure needs to change. What I need to do is copy the data to a new table and add a sequence that is only for the item_id. So the result will be:

Table: NEW_TABLE

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

In the case the key will be: item_id and seq.

I am currently doing this in SQL Server, but please try to keep the solution as close to ANSI SQL as possible because I will eventually need Oracle and PostgreSQL versions, too, for our clients that use those. But to be clear, my immediate concern is SQL Server.

I'm fine using CTEs, I just can't think of how to do this right now...

tsql sql-server sql
1 Answers

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.

user_11440350
Related