Need to combine muliple rows of data into 1 NTEXT field in different table

I have a table that stores multiple repsonse for the same action.  The table also has an indentity column.

What I need to do is combine all the responses for a particular action into another table's ntext colum based on the unique action.

The problem I have is that the response field in both tables is ntext.  I know I need to use UPDATETEXT with a cusor, but am hoping that there is some sample code out there...

So what I have would be one action, ActionId = 3 that has 3 reponses.  I need to combine those 3 response and then write that back to the 2nd table.

Who is Participating?
LowfatspreadConnect With a Mentor Commented:

declare @ctr int
set @ctr=1
declare @Norows int

create table #temp ( tid int identity(1,1),actionid int)
Insert into #temp (actionid)
select actionid
 from Responsetable
group by actionid
having count(*) > 1

Declare @actid int
Declare @rid int
DECLARE @ptrval varbinary(16)
DECLARE @newptr varbinary(16)

set @Norows=@@rowcount
while @ctr <=@norows
   select @Actid=Actionid,@ctr=@ctr+1,@rid=0 from #temp where Tid=@ctr
  While Exists (
   SELECT Actionid
      from ResponseTable
     where Actionid=@Actid
        and @rid < ResponseIdentityColumn
    order by ResponseIdentityColumn)
   SELECT Top 1 @ptrval = TEXTPTR(Response),@rid=ResponseIdentityColumn
      from ResponseTable
     where Actionid=@Actid
        and @rid < ResponseIdentityColumn
    order by ResponseIdentityColumn
   Select @Newptr = TEXTPTR(Response)
     from NewTable
     Where ActionID=Actid
   UPDATETEXT NEWTable.Response @Newptr, NULL , 0 ,ResponseTable.Response @ptrval
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.