Combining many records into 1

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need help
with the select statement.

example tables:
CREATE TABLE [NoteHeader] (
[NoteID] [int],
[CustomerID] [int] ,
[Desc1] [varchar] (255),
[Date] [datetime] ,
)
GO

CREATE TABLE [NoteDetail] (
[NoteId] [int],
[SeqNum] [int] NOT NULL ,
[Note1] [varchar] (255),
[Note2] [varchar] (255),
[Note3] [varchar] (255),
[Note4] [varchar] (255),
[Note5] [varchar] (255)
)
GO


Sample script joining tables:
SELECT *
FROM NoteHeader INNER JOIN
NoteDetail ON NoteHeader.NoteID = NoteDetail.NoteId

Sample results:
NoteID CustomerID Desc1 Date
Note1 Note2
.....Note5
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 Notes detail record 1 field2 .....
1111 987 Note Header Description 2007-07-15
Notes detail record 2 field 1 Notes detail record 2 field 2


Desired results:
NoteID CustomerID Desc1 Date
CombinedNotes
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 +

Notes detail record 1 field2 +

Notes detail record 2 field 1 +

Notes detail record 2 field 2 +

through unlimited number of records up to 5
fields each



The NoteID field is the unique number. 1 record per NoteID in NoteHeader,
NoteDetail can have unlimited number of same NoteID (usually not more than
10)

Re: Combining many records into 1

rdraider (rdraider@sbcglobal.net) writes:
> Using SQL 2000, how can you combine multiple records into 1?
> The source data is varchar(255), the destination will be text. I need
> help with the select statement.

SQL Server MVP Anith Sen as a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.


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

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

Re: Combining many records into 1

Thanks for the info. My problem is the resulting data will be too large for
varchar(8000). All these examples seem to use varchar(8000)
I need to convert to a text datatype. I can concat multiple varchar fields
from 1 record into text but the problem is how the source data is
structured.
The source data is from an app called 'Onyx' running SQL 6.5 (I'm naming
names !!). I upgraded the SQL 6.5 to SQL 2000. I don't hav SQL 2005.

There is blog style data in the form of:

NoteID + NoteDetailID + SeqNum + Note1 + Note 2 + Note3 + Note4 + Note 5

NoteID is the same for each complete blog record
NoteDetailID changes for each new entry to the blog
SeqNum ranges from 1 - 20 for each NoteDetailID
The blog entries are stored in Note1 through Note5 - each only varchar(255)
As each Note field fills up, it rolls to Note2, Note3, Note4, Note5, then
creates a new record with same NoteID and NoteDetailID.
A new NoteDetailID is created when a new entry is started (such as a user
adds more to the blog days later)

I assume it was designed this way because SQL 6.5 largest data type was
varchar(255) ?

I have already created a temp table to combine the Note1 + Note 2 + Note3 +
Note4 + Note 5 into 1 varchar(8000) field.
Still looking for a method to properly combine the blog entries to text in
the proper order.

Thanks again for any ideas.

RD

"Erland Sommarskog" wrote in message
news:Xns9970F2CF7D6CYazorman@127.0.0.1...
> rdraider (rdraider@sbcglobal.net) writes:
>> Using SQL 2000, how can you combine multiple records into 1?
>> The source data is varchar(255), the destination will be text. I need
>> help with the select statement.
>
> SQL Server MVP Anith Sen as a couple of methods on
> http://www.projectdmx.com/tsql/rowconcatenate.aspx.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Combining many records into 1

rdraider (rdraider@sbcglobal.net) writes:
> Thanks for the info. My problem is the resulting data will be too large
> for varchar(8000). All these examples seem to use varchar(8000)
> I need to convert to a text datatype. I can concat multiple varchar fields
> from 1 record into text but the problem is how the source data is
> structured.
> The source data is from an app called 'Onyx' running SQL 6.5 (I'm naming
> names !!). I upgraded the SQL 6.5 to SQL 2000. I don't hav SQL 2005.

I think you have two options:

1) Get SQL 2005.
2) Do it client-side.

I think you can do it on SQL 2000, but then you would have to run
a cursor, and use WRITETEXT and UPDATETEXT and it would be very very
painful. Please don't ask me to write the code for you, but if you
have problems with using WRITETEXT and UPDATETEXT, I can try to assist.

> I assume it was designed this way because SQL 6.5 largest data type was
> varchar(255) ?

Yes, that is correct.


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

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