Hi All,
I am trying to add a running number to differentiate between set of rows belonging to a distinct ID. tried Rank and row number but had no success. my source table is as following
create table #SourceTable (ID int, SomeText varchar(50))
insert into #SourceTable values (1111,'Header1')
insert into #SourceTable values (1111,'dtl1')
insert into #SourceTable values (1111,'dtl2')
insert into #SourceTable values (1111,'dtl3')
insert into #SourceTable values (1111,'Header2')
insert into #SourceTable values (1111,'dtl1')
insert into #SourceTable values (1111,'dtl2')
insert into #SourceTable values (1111,'Header3')
insert into #SourceTable values (1111,'dtl1')
insert into #SourceTable values (1111,'dtl2')
insert into #SourceTable values (2222,'Header1')
insert into #SourceTable values (2222,'dtl1')
insert into #SourceTable values (2222,'dtl2')
insert into #SourceTable values (2222,'Header2')
insert into #SourceTable values (2222,'dtl1')
insert into #SourceTable values (9990,'Header1')
insert into #SourceTable values (9990,'dtl1')
insert into #SourceTable values (9990,'dtl2')
select * from #SourceTable
drop table #SourceTable
and my Desired output is as following
create table #DesiredTable (ID int, SomeText varchar(50),Identifier int)
insert into #DesiredTable values (1111,'Header1',1)
insert into #DesiredTable values (1111,'dtl1',1)
insert into #DesiredTable values (1111,'dtl2',1)
insert into #DesiredTable values (1111,'dtl3',1)
insert into #DesiredTable values (1111,'Header2',2)
insert into #DesiredTable values (1111,'dtl1',2)
insert into #DesiredTable values (1111,'dtl2',2)
insert into #DesiredTable values (1111,'Header3',3)
insert into #DesiredTable values (1111,'dtl1',3)
insert into #DesiredTable values (1111,'dtl2',3)
insert into #DesiredTable values (2222,'Header1',1)
insert into #DesiredTable values (2222,'dtl1',1)
insert into #DesiredTable values (2222,'dtl2',1)
insert into #DesiredTable values (2222,'Header2',2)
insert into #DesiredTable values (2222,'dtl1',2)
insert into #DesiredTable values (9990,'Header1',1)
insert into #DesiredTable values (9990,'dtl1',1)
insert into #DesiredTable values (9990,'dtl2',1)
select * from #DesiredTable
drop table #DesiredTable
Note: The Text (Header1, Header2, Header3) will always be constant and they will appear in the same ascending order for a given ID. the details will be changing.
how can i achieve this? Appreciate any help.