build a line number column in a table

  • talziv

    Grasshopper

    Points: 20

    Hi all

    I need to build a line number column in a table

    MemberChildKey int

    MemberKey int

    ChildId int

    ChildName int

    LineNumber int

    The Column LineNumber should be filled separately for each MemberKey, and always starting from 1

    Thanks all for your time

    Tal

  • Antares686

    SSC Guru

    Points: 125444

    Can you provide an example. This is not clear to me.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • talziv

    Grasshopper

    Points: 20

    Here it is:

    MemberChildKey,MemberKey,CId,CName,LineNumber

    1 1 123 A 1

    2 1 456 B 2

    3 2 789 C 1

    4 2 234 D 2

    5 2 345 E 3

  • rcavill

    Default port

    Points: 1482

    You would need a cursor to step thru each MemberKey value, then update the table the incrementing sequence number within each MemberKey. This is done with the "... set ... @mbrseq=@mbrseq+1 " in the update statement.

    /* start of script */

    declare @mbrid int -- current member

    declare @mbrseq int -- sequence number for current member

    declare @continue int

    select @continue = 1

    declare cMember Cursor for select distinct MemberKey from myTable

    open cMember

    while @continue = 1 begin

    /* next cursor read */

    fetch next from cMember into @mbrid

    if @@FETCH_STATUS <> 0 begin

    select @continue = 0

    break

    end

    /* update sequence numbers for current member */

    select @mbrseq=0

    update myTable

    set LineNumber=@mbrseq,

    @mbrseq=@mbrseq+1

    where MemberKey = @mbrid

    end

    close cMember

    deallocate cMember

    /*end of script */

    Robert

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply