May 28, 2002 at 3:29 pm
I have a table which Contains fields like
anumber, firstname, lastname
now anumber can be repeated, such as i can have 2 records like
anumber firstname lastname
232851LUZGLORIA
232851HERMANGLORIA
when the case number is same I want to merge the two records such taht the only different information gets added to one record....such that if i Merge the above 2 records, i Shd get
anumberfirstnamelastnamecdFirstNamecdLastName
232851LUZGLORIA HERMANGLORIA
Is it possible to do that ?
any suggestions how?
May 29, 2002 at 3:14 am
It is possible. Not very practical but possible.
One thing that might make it more impractical is the number of repeats. Ideally, it would be good if you know the maximum number of repeats. The lower it is the better because for each repeat you will need two extra columns.
I won't list the code but here are the basic steps that you might won't to try out:
(1) Find the maximum number of repeats.
(2) Create a Temporary table with the required number of columns. e.g. If the max number of repeats is 2 then build a table with 5 columns, "anumber", "FirstName0", "LastName0", "FirstName1", "LastName1".
(3) Cursor through your original table and fetch the anumber, firstname and lastname values. If the anumber doesn't exist in your temp table, insert it into the temp table together with the firstname and lastname.
(4) Fetch the next record. If the anumber does exist in the temp table then update the relevant record in the temp table and set the value of firstname1 and lastname1 to the value of firstname and lastname that you obtained from the cursor fetch.
You will need a parameter called something like @Oldanumber. You will use this to compare the newly fetched anumber in order to determine whether it's a repeat or not. If it isn't, then reset the @Oldanumber to the newly fetched number.
Once you have cursored through the whole table just do a select * from #TempTable.
That should do it (I Think),
Karl
Karl Grambow
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply