April 19, 2002 at 7:47 am
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
April 19, 2002 at 8:57 am
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)
April 19, 2002 at 9:39 am
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
June 3, 2002 at 7:56 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply