Hello,

Thanks for taking the time to help me out with this. I've been pulling
my hair out trying to figure this out.

SERVER: Microsoft SQL Server 2000

TABLE STRUCTURE:
terms_no varchar 10
note_no int 4
note_text varchar 60

SAMPLE DATA:
terms_no | note_no | note_text
2 | 1 | This is a test.
2 | 2 | This is a test second line.
3 | 1 | Another test.

THE ISSUE:
There can be multiple note_text(s) for each term_no, which can be
sorted by the note_no. I would like to take each and combine them into
one string. There can be an unlimited amount of note_no(s) and
term_no(s).

DESIRED RESULT:
If the terms_no is 2 then combine the two lines into one string like
the following:

note_text_combined
This is a test. This is a test second line.

Any ideas on how I can accomplish this through SQL I would very much
appreciate it. If I had the choice to redesign this table I would
without question, however, it can't be done.

Thanks again ! I really appreciate it !

cwwilly

(cwwilly@gmail.com) writes:
> Thanks for taking the time to help me out with this. I've been pulling
> my hair out trying to figure this out.
>
> SERVER: Microsoft SQL Server 2000
>
> TABLE STRUCTURE:
> terms_no varchar 10
> note_no int 4
> note_text varchar 60
>
> SAMPLE DATA:
> terms_no | note_no | note_text
> 2 | 1 | This is a test.
> 2 | 2 | This is a test second line.
> 3 | 1 | Another test.
>
> THE ISSUE:
> There can be multiple note_text(s) for each term_no, which can be
> sorted by the note_no. I would like to take each and combine them into
> one string. There can be an unlimited amount of note_no(s) and
> term_no(s).
>
> DESIRED RESULT:
> If the terms_no is 2 then combine the two lines into one string like
> the following:
>
> note_text_combined
> This is a test. This is a test second line.
>
> Any ideas on how I can accomplish this through SQL I would very much
> appreciate it. If I had the choice to redesign this table I would
> without question, however, it can't be done.

In SQL 2000, you need to do this with an iterative solution, although
for some speed, you could accumulate all note_no = 1 at once.

It's not clear where you will save the result, but you use a varchar(8000) -
and hope that you don't run out of space.

If the result for one termns_no can exceed 8000 characters, you need to
do this client-side.


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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp