Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-22
1
Medium Priority
?
321 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
Comment
Question by:DougR_MS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 total points
ID: 12384524
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

604 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