Re: Combine Multiple Records

From: Ilya Margolin (ilya_no_spam__at_unapen.com)
Date: 08/10/04 

Date: Tue, 10 Aug 2004 08:51:42 -0400

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@yahoo.com> wrote in message
news:ekOCiymfEHA.596@TK2MSFTNGP11.phx.gbl...
> 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
>
>

Relevant Pages