Posted by Erland Sommarskog on 12/06/05 00:20

imani_technology_spam@yahoo.com (imani_technology_spam@yahoo.com) writes:
> I have the following table;
>
> CREATE TABLE [x_Note] (
> [x_NoteId] [int] IDENTITY (1, 1) NOT NULL ,
> [Note] [varchar] (7200) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS
> NOT
> NULL ,
>
> CONSTRAINT [PK_x_NoteId] PRIMARY KEY CLUSTERED
> (
> [x_NoteId],
> ) WITH FILLFACTOR = 90 ON [USERDATA] ,
>
> ) ON [USERDATA]
> GO
>
> My clients want me to take the contents of the Note column for each row
> and combine them. In other words, they basically want:
>
> Note = Note [accumulated from previous rows] + Char(13) [because they
> want a carriage return] + Note [from current record].
>
> What is the most efficient and relatively painless way to do this? I
> think it might require a cursor, but I'm not sure if there is a more
> elegant set-based method to make this happen.

In SQL 2000, a cursor is the only defined way to do this, but you
can only compose a string which is 8000 chars long.

In SQL 2005, you can do this painlessly, thanks to some syntax from
the XML corner of SQL 2005:

select substring(List, 1, datalength(List)/2 - 1)
-- strip the last CR from the list
from
(select Note + char(13) as [text()]
from x_Note
order by x_NoteId
for xml path('')) as Dummy(List)

A bit obscure, but it works!


--
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]