CODEWITHSUNDEEP
sql-server-2008t-sql
Hemant Sisodia
Hemant Sisodia

Reputation: 498

How to update the Group Number in a sequence in all rows

I have a table in which the data is stored as:

enter image description here

I want only the Branch column to be updated in a way that the sequence becomes continuous. Means the rows from ID 1 to 4 would have same value, but the rows from ID 5 to 7 will have Branch as 3, rows 8 and 9 would have Branch as 4, rows from 10 to 12 would have branch as 5 and so on.

My desired output would look like this:

enter image description here

I don't want the rows to be reordered, means the rows would have same sequence as they are now with continuous increasing ID column, and only the Branch column to be ordered. I tried it doing with looping but that part is becoming so large and error prone that I was thinking of some other direct approach. Is it possible through CTEs or any other approach? How can I do so?

Upvotes: 0

Views: 452

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SQL DEMO

I use more columns than necesary just to show what is going on. rn is just to show how the grp is create. You only need grp to the final result.

The idea is create a group sequence based on Id. Then using DENSE_RANK() you get your desire sequence.

This assume ID is sequential number without holes, if your ID has holes, you need to use ROW_NUMBER() to create a sequence.

WITH cte as (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [Branch] ORDER BY [Id]) as rn,
           [Id] - ROW_NUMBER() OVER (PARTITION BY [Branch] ORDER BY [Id]) as grp
    FROM Table1
)    
SELECT *, DENSE_RANK() OVER (ORDER BY grp) as new_branch
FROM cte

OUTPUT

enter image description here

Upvotes: 2

Related Questions