Avatar of Scott Baldridge
Scott Baldridge
 asked on

Concat Notes with OUTER APPLY

Hello, I have the following Outter Apply that is used to get 1 of possible many notes. I need to change statement to concatenate all the notes while adding a visible separator which could be a line break some ***** and a line break to have separation between notes. What would be the best way to accomplish? How to modify the following?

OUTER APPLY (                              
                                    SELECT TOP(1) n.NoteText  
                                          FROM OBJECT_NOTE n
                                          WHERE n.NoteType = 4
                                          AND n.OwnerObjectRegistryId = cs.ObjectRegistryId
                                          ORDER BY n.EntryDate DESC       
                                    )
                        AS FrsNote
Microsoft SQL Server 2008Microsoft SQL ServerMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ste5an

Use FOR XML. E.g.

DECLARE @Result VARCHAR(MAX);

SET @Result = ( SELECT	STUFF((SELECT	', ' + name
	FROM	sys.tables
	FOR XML PATH('')), 1, 2, '' ));
PRINT @Result;

PRINT '----';

DECLARE @Terminator VARCHAR(255) = '||***';
SET @Result = ( SELECT	REPLACE(STUFF((SELECT	@Terminator + name
	FROM	sys.tables
	FOR XML PATH('')), 1, LEN(@Terminator), '' ), '||', CHAR(13) + CHAR(10)));
PRINT @Result;

Open in new window

Scott Baldridge

ASKER
Not doing XML, the outer apply is part of a much larger select statement where it returns one value. I need that value to be a concatenation of multiple NoteText from the outer apply join condition. Or am I missing something with your solution?
Mike Eghtebas

Where is the first part of this outer apply?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
ste5an

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Scott Baldridge

ASKER
Thanks for your assistance!
PortletPaul

A small note of caution here. Because this concatenation technique is using XML then IF your data contains characters that XML needs, you can end up with "XML entities" in the output

e.g. IF the data was
"Peter, Paul & Mary"             it could end up looking like
"Peter, Paul & Mary"

To protect against this just tweak the for xml path approach as shown below

SELECT
      T.name
    , X.x
FROM sys.tables T
      OUTER APPLY (
            SELECT
                  STUFF((
                        SELECT
                              ', ' + C.name
                        FROM sys.columns C
                        WHERE C.object_id = T.object_id
                        FOR xml PATH (''), TYPE --<< note
                  )
                  .value('.', 'varchar(max)') --<< note
                  , 1, 2, '')
      ) X (x);