Posted by Erland Sommarskog on 12/07/05 23:40

imani_technology_spam@yahoo.com (imani_technology_spam@yahoo.com) writes:
> Now, I have NO idea how to pull this off. I was considering nested
> cursors, but I'm not sure if that is the best way, or even a practical
> way. Help!!!

Before just posting "I can't do this, I can't do that", how about doing
some research. How long will these combined texts be? Since the notes
are declared as varchar(7200), one suspects that the total length may
exceed 8000 chars. But is it really so? What does:

SELECT NoteCategory, SUM(len(Note) + 1)
FROM x_Note
ORDER BY 2 DESC

return?

If there commonly are categories where the total length is > 8000, you
will have to run a cursor, and accumlate data into a new table, where
the concatenated note is written into a text column using the WRITETEXT
command. Quite painful. You don't need nested cursors though, your loop
only needs some logic to recognize that you have entered a new NoteCategory.

By the way, within each category, in which order are you supposed to
concatenate them? By x_NoteId? Are you sure that gives a correct result?



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]