I have a table like this

And I'd like to rename values like this

All values in dept are grouped by id, same departments repeat each group. I'm unsure how to make a loop that does one pass per id (replace 'b' with 'b1'), then a second pass (replace 'b' with 'b2').

Or does once per table, but each statement ends after one replace per id.

Thanks in advance!

CodePudding user response：

We don't need to loop. We can just use row_number().

select  id
       ,case when dept = 'b' then dept||row_number() over(partition by id, dept) else dept end as dept
from    t

Fiddle