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!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;
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?
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 );
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);
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);
Premium Content
You need an Expert Office subscription to comment.Start Free Trial