SQL Statement Help

[cwwilly]

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

[Erland Sommarskog]

(cw*****@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, es****@sommarskog.se

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