CODEWITHSUNDEEP
sqlsql-servert-sql
Loobe
Loobe

Reputation: 21

SQL: Increment column value foreach id (loop?)

I have a table like this:

Table

that I made into a big stored-procedure to do a pivot in the end. What I want to do is fill the 'index' column like an identity column BUT it must re-start from '1' on each ID like

this.

I think it must be some kind of foreach(id)

PS: the ID are not always 3.. it's for the example.

Upvotes: 0

Views: 297

Answers (1)

phillyd
phillyd

Reputation: 797

Using ROW_NUMBER() with PARTITION BY would seem like a good method to create your Index column. Something like this:

SELECT
    ID 
    , [Time]
    , IDx
    , ROW_NUMBER() OVER(PARTITION BY ID
           ORDER BY IDx ASC) AS "Index"
FROM
    myTable;

Upvotes: 3

Related Questions