title: SQL query with subquery and concatenating variable using CRM on-premise data
body: <h2>I am working on a report where I need to provide a summary of notes for particular &quot;activities/tasks&quot;.</h2>
  <p>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:</p>
  <blockquote>
  <p>[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.</p>
  </blockquote>
  <p><strong>The data comes from replicated tables of our on-premise CRM system, and I'm using SQL Server 2012.</strong>  The tables I'm using are: <strong>AnnotationBase</strong> (contains the notes), <strong>ActivityPointerBase</strong> (contains the activities/tasks), and <strong>SystemUserID</strong> (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).</p>
  <p><strong>I have written the following query:</strong></p>
  <pre><code>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%'
  </code></pre>
  <p>I know the above method is probably not very efficient, but the list of &quot;Projects On Hold&quot; 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:</p>
  <pre><code>Msg 102, Level 15, State 1, Line 3
  Incorrect syntax near '='.
  Msg 102, Level 15, State 1, Line 10
  Incorrect syntax near ')'.
  </code></pre>
  <p><strong>I envision two possible solutions to my problem:</strong></p>
  <ol>
  <li>My subquery errors are fixed <strong>or</strong></li>
  <li>Create a &quot;view&quot; of just the concatenated NotesText, grouped by ActivityId (which would work as a key), and then just query from that.</li>
  </ol>
  <p>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.</p>