declare @a table(id nvarchar(10),num int)
insert into @a
select '000019',1 union all
select '000019',0 union all
select '000019',0 union all
select '000019',0 union all
select '000020',0 union all
select '000020',0 union all
select '000020',0 union all
select '000021',0 union all
select '000021',0 union all
select '000021',0
select identity(int,1,1) iden,id into #a from @a
select
num= isnull((select case when iden is null then 0 else
(select COUNT(*) from #a c where c.id = B.id and c.iden <= B.iden)
end from #a b where a.iden = b.iden + 1 and B.id = a.id ),0)+1
,id from #a a
drop table #a
insert into @a
select '000019',1 union all
select '000019',0 union all
select '000019',0 union all
select '000019',0 union all
select '000020',0 union all
select '000020',0 union all
select '000020',0 union all
select '000021',0 union all
select '000021',0 union all
select '000021',0
select identity(int,1,1) iden,id into #a from @a
select
num= isnull((select case when iden is null then 0 else
(select COUNT(*) from #a c where c.id = B.id and c.iden <= B.iden)
end from #a b where a.iden = b.iden + 1 and B.id = a.id ),0)+1
,id from #a a
drop table #a