469,110 Members | 1,957 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,110 developers. It's quick & easy.

SQL Statement Help

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

Jul 23 '05 #1
1 876
(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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by James E Koehler | last post: by
2 posts views Thread by Little PussyCat | last post: by
11 posts views Thread by Scott C. Reynolds | last post: by
10 posts views Thread by John Smith | last post: by
7 posts views Thread by Steven Bethard | last post: by
18 posts views Thread by dspfun | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.