SQL query with subquery and concatenating variable using CRM on-premise data

Question

I am working on a report where I need to provide a summary of notes for particular "activities/tasks".

Since the activity can accept multiple notes, I have to search for all the notes related to that activity. I then order it by date (new to old), and concatenate them with some other strings as such:

[Tom Smith wrote on 9/23/2016 1:21 pm] Client was out of office, left message. [Jane Doe wrote on 9/21/2016 3:24 pm] Client called asking about pricing.

The data comes from replicated tables of our on-premise CRM system, and I'm using SQL Server 2012. The tables I'm using are: AnnotationBase (contains the notes), ActivityPointerBase (contains the activities/tasks), and SystemUserID (to lookup usernames). Due to Data mismatch, I have to do some converting of the data types so that I can concatenate them properly, so that's why there's a lot of CAST and CONVERT. In addition, not all Activities have a NoteText associated with them, and sometimes the NoteText field is NULL, so I have to catch and filter the NULLs out (or it'll break my concatenated string).

I have written the following query:

DECLARE @Notes VarChar(Max)
  SELECT 
(   SELECT TOP 5 @Notes = COALESCE(@Notes+ ', ', '') +  '[' + CONVERT(varchar(max), ISNULL(sUB.FullName, 'N/A')) + ' wrote on ' + CONVERT(varchar(10), CAST(Anno.ModifiedOn AS DATE), 101) + RIGHT(CONVERT(varchar(32),Anno.ModifiedOn,100),8) + '] ' + CONVERT(varchar(max), ISNULL(Anno.NoteText, '')) --+ CONVERT(varchar(max), CAST(ModifiedOn AS varchar(max)), 101)--+ CAST(ModifiedOn AS varchar(max))
    FROM [CRM_rsd].[dbo].[AnnotationBase] AS Anno
    LEFT OUTER JOIN [CRM_rsd].[dbo].[systemUserBase] AS sUB
    ON Anno.ModifiedBy = sUB.SystemUserId 
    WHERE Anno.ObjectId = Task.ActivityId--'0B48AB28-C08F-419A-8D98-9916BDFFDE4C' 
    ORDER BY Anno.ModifiedOn DESC
    SELECT LEFT(@Notes,LEN(@Notes)-1) 
) AS Notes
,Task.*
  FROM [CRM_rsd].[dbo].[ActivityPointerBase] AS Task
  WHERE Task.Subject LIKE '%Project On Hold%'

I know the above method is probably not very efficient, but the list of "Projects On Hold" is rather small (less than 500), so performance isn't a priority. What is a priority is to be able to get a consolidated and concatenated list of notes for each activity. I have been searching all over the internet for a solution, and I have tried many different methods. But I get the following errors:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ')'.

I envision two possible solutions to my problem:

1. My subquery errors are fixed or
2. Create a "view" of just the concatenated NotesText, grouped by ActivityId (which would work as a key), and then just query from that.

Yet even though I'm pretty sure my ideas would work, I can't seem to figure out how to concatenate a column and group at the same time.

Answer 1

What you are trying to do is display the records from one table (in your case ActivityPointerBase) and inside you want to add a calculated column with information from multiple records from another table (in your case AnnotationBase) merged in the rows.

There are multiple ways how you could achieve this that are different in terms of performance impact:

Option 1. You could write a scalar function that would receive as parameter the id of the task and would inside select the top 5 records, concatenating them in a procedural fashion and returning a varchar(max)

Option 2: You could use a subquery in combination with the FOR XML clause.

SELECT 
    SUBSTRING(
        CAST(
               (SELECT TOP 5 ', ' +  
                       '[' + CONVERT(varchar(max), ISNULL(FullName, 'N/A')) +
                       ' wrote on ' + 
                       CONVERT(varchar(10), CAST(ModifiedOn AS DATE), 101) +
                       RIGHT(CONVERT(varchar(32),ModifiedOn,100),8) + '] ' +
                       CONVERT(varchar(max), ISNULL(NoteText, ''))
                FROM [CRM_rsd].[dbo].[AnnotationBase] AS Anno
                     LEFT OUTER JOIN [CRM_rsd].[dbo].[systemUserBase] AS sUB ON Anno.ModifiedBy = sUB.SystemUserId 
                WHERE Anno.ObjectId = Task.ActivityId 
                ORDER BY Anno.ModifiedOn DESC
                FOR XML PATH(''),TYPE
               ) AS VARCHAR(MAX)
        ),3,99999) AS Notes
   ,Task.*
FROM [CRM_rsd].[dbo].[ActivityPointerBase] AS Task
WHERE Task.Subject LIKE '%Project On Hold%'

What here happens is that by using the construct inside the CAST() we fetch the top 5 lines and make SQL server produce an XML with no element names, resulting in concatenation of the element values, we add comma as separator. Then we convert the XML to varchar(max) and remove the initial separator before the first record.

I prefer option 2, it will perform much better then using a scalar function.