Solved

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

Posted on 2004-10-22
261 Views
Last Modified: 2012-05-05
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.

Thanks,
Doug
0
Question by:DougR_MS
    1 Comment
     
    LVL 50

    Accepted Solution

    by:
    try

    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
    begin
       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)
       Begin
       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
       End
    End
       
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Course: Foundations of Front-End Development

    Jump-start a lucrative career in front-end web development, with zero previous coding experience required. This course covers the basic programming concepts and languages required for creating engaging websites from scratch.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    875 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now