Concat Notes with OUTER APPLY

Scott Baldridge
Scott Baldridge used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
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

Author

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 Developer

Commented:
Where is the first part of this outer apply?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Senior Developer
Commented:
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

Author

Commented:
Thanks for your assistance!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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);

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial