|
|
Posted by David Portas on 12/07/05 21:51
imani_technology_spam@yahoo.com wrote:
> Okay, now I have a new variation to this problem. Let's change the DDL
> slightly:
>
> CREATE TABLE [x_Note] (
> [x_NoteId] [int] IDENTITY (1, 1) NOT NULL ,
> [NoteCategory] [int] 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
>
> Instead of combining the contents of the Note column for each row, we
> need to combine the Notes within each NoteCategory. In other words, I
> need to combine the Notes for all rows of NoteCategory #1, all the
> Notes for NoteCategory #2, and so on.
>
> In other words, FOR EACH NoteCategory, the clients want:
>
> Note = Note [accumulated from previous rows within the NoteCategory] +
> Char(13) [because they want a carriage return] + Note [from current
> record within the NoteCategory].
>
> Now, I have NO idea how to pull this off. I was considering nested
> cursors, but I'm not sure if that is the best way, or even a practical
> way. Help!!!
You still didn't tell us what version of SQL Server you are using. Nor
have you explained why you can't do this client side.
Try:
SELECT notecategory,
MAX(CASE seq WHEN 1 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE seq WHEN 2 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE seq WHEN 3 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE seq WHEN 4 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE seq WHEN 5 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE seq WHEN 6 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE seq WHEN 7 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE seq WHEN 8 THEN note+CHAR(13) ELSE '' END)+
MAX(CASE seq WHEN 9 THEN note+CHAR(13) ELSE '' END)
/* ... etc */
AS note
FROM
(SELECT T1.notecategory, T1.x_noteid, T1.note, COUNT(*) seq
FROM x_note AS T1
JOIN x_note AS T2
ON T1.notecategory = T2.notecategory
AND T1.x_noteid >= T2.x_noteid
GROUP BY T1.notecategory, T1.x_noteid, T1.note) AS T
GROUP BY notecategory ;
--
David Portas
SQL Server MVP
--
[Back to original message]
|