LOOP through column to replace same value with different names SQL

Question

I have a table like this

id	dept
1	a
1	b
1	b
2	a
2	b

And I'd like to rename values like this

id	dept
1	a
1	b1
1	b2
2	a
2	b1

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!

What is the primary key of your table? To perform such update, we would need a PK. — GMB
– GMB, Commented Nov 4, 2022 at 18:28
Yes, but id is not unique, so it can't be a PK. That's the problem here. — GMB
– GMB, Commented Nov 4, 2022 at 18:32
Clarification: each row needs a unique id? If I were to insert a running sum column as pk, what would the solution look like? — Dustin
– Dustin, Commented Nov 4, 2022 at 18:36

DannySlor · Accepted Answer · 2022-11-04 19:18:24Z

1

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

id	dept
1	a
1	b1
1	b2
2	a
2	b1

Fiddle

answered Nov 4, 2022 at 19:18

DannySlor

4,6201 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

LOOP through column to replace same value with different names SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related