I have a table that has comments in it, but the way they are formatted isn't
very easy to work with. For Example:

Comment# Seq# Comment
12345 1 Please ship this
12345 2 order today.

What is shown is comment # 12345, which has 2 records (They can have many)
to make the comment "Please ship this order today"

I would like to use a DTS job to transform this comment into one record and
eliminate the seq #. The above would become:

Comment# Comment
12345 Please ship this order today.

Does anyone know how to accomplish this?

Thanks,
Mike

Mike,

You would have to do it in a loop or in a cursor. Here is a loop example:

Declare @MaxSeg smallint
,@Counter smallint

Select @MaxSeg = Max([Seq#]) From <Comments table>

Set @Counter = 1

While @Counter <= @MaxSeg Begin

Set @Counter = @Counter + 1

Update c1
Set Comment = Comment + c2.Comment
From <Comments table> c1
Inner Join <Comments table> c2
On c1.[Comment#] = c2.[Comment#]
Where c1.[Seq#] = 1
And c2.[Seq#] = @Counter

Delete <Comments table>
Where [Seq#] = @Counter
End

The example will stock all comments into Seq# = 1 rows and get rid of the
rest of Seq#. The code was not tested.

Ilya

"Mike" <mbaith (AT) yahoo (DOT) com> wrote

How would I execute this code from a DTS job?

Mike,

You make it an Execute SQL task with that code or with a stored procedure
made out of the code.

Ilya

"Mike" <mbaith (AT) yahoo (DOT) com> wrote

....make sure the Comment column has enough space in it.

Ilya

"Ilya Margolin" <ilya_no_spam_ (AT) unapen (DOT) com> wrote