Posted by David Portas on 12/05/05 22:37

imani_technology_spam@yahoo.com wrote:

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

This looks pretty odd. If the average size of data in the Note column
is just half of your maximum size then you'll only be able to
concatenate two rows before you break the 8000 character ceiling. This
means you will most likely have to return a result set rather than a
variable (TEXT variables not allowed). But you want each value
delimited with carriage returns and most clients will display a
multiple row result set as multiple lines anyway - so what can you hope
to gain from concatenating them?

The above comments apply to SQL Server 2000. In SQL Server 2005 you can
do some fancy stuff like the following. Please always specify what
version you are using so that we don't have to guess.

WITH X (note, row_no)
AS
(
SELECT CAST(note AS VARCHAR(MAX)),
ROW_NUMBER() OVER (ORDER BY x_noteid)
FROM x_note
)
SELECT
MAX(CASE row_no WHEN 1 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE row_no WHEN 2 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE row_no WHEN 3 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE row_no WHEN 4 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE row_no WHEN 5 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE row_no WHEN 6 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE row_no WHEN 7 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE row_no WHEN 8 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE row_no WHEN 9 THEN note+CHAR(13) ELSE '' END)
/* ... etc */
AS note
FROM X ;

SELECT REPLACE( REPLACE(
(SELECT note
FROM x_note
ORDER BY
x_noteid
FOR XML PATH (''))
, '<note>', CHAR(13)),'</note>','') AS note ;

IMO clientside is the best option though.

--
David Portas
SQL Server MVP
--

[Back to original message]