How about something with no CTE nor subquery...
I'll borrow the population of test data...
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
VendID char(3),
Type int
)
insert #Something
select 'ABC', 1 union all
select 'ABC', 0 union all
select 'ABC', 0 union all
select 'XYZ', 1 union all
select 'XYZ', 0 union all
select 'XYZ', 0 union all
select 'XYZ', 0;
Here's the code for your required output
select
vendid, type,
new_vendid = vendid + case when type = 1 then '' else '-' + right('0' + cast(row_number() over(partition by vendid order by vendid asc, type desc) - 1 as varchar(2)),2) end
from #something
Happy Coding!!!
~~ CKK