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
Scott BaldridgeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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 BaldridgeAuthor Commented:
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 EghtebasDatabase and Application DeveloperCommented:
Where is the first part of this outer apply?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Get the Guide
ste5anSenior DeveloperCommented:
Just place the concept (basically whats right from SET @Result = ) with your tables and columns into your OUTER APPLY...

  
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('')), 1, 2, '' )
	) X ( x );

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott BaldridgeAuthor Commented:
Thanks for your assistance!
PortletPaulEE Topic AdvisorCommented:
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);
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.