sql server - Generate Group/Segment Identity with CTEs -

sql server - Generate Group/Segment Identity with CTEs -

the problem having generate serial groupid in column a(segment) starts identify rows till next c of column b(indicator) segment. column b indicator of segment start(a) , segment end (c).. in between (b or x) should marked segment id including rows , c. hope clear enough.

file received

processed file generated group/segment identity

i need suggestions best solution problem. have .net loop doing experimenting ctes. please help.

if table has id column, , table called [stuff], works:

-- set segment values rows indicator = 'a' update [stuff] set segment = s.row (select row_number() over(order id) row, id [stuff] indicator = 'a' ) s s.id = [stuff].id -- update other rows update [stuff] set segment = ( select top 1 segment [stuff] s1 s1.segment not null , s1.id <= [stuff].id order id desc ) update [stuff] set segment = -1 indicator = '#' update [stuff] set segment = 0 indicator = '##'

sql-server tsql recursion sql-server-2012 common-table-expression