title: Concatenate multiple rows in one column
body: <p>I have a query that produces the following results</p>
  
  <pre><code>NAME         WeekPattern
  
  John Smith   
  John Smith   //
  John Smith   OO
  Jack Jones   
  Jack Jones   O
  Jack Jones   //
  </code></pre>
  
  <p>Is there a way I can concatenate the WeekPattern column so the results will show like this:</p>
  
  <pre><code>NAME         WeekPattern
  
  John Smith     // OO
  Jack Jones     O //
  </code></pre>
  
  <p>I have tried using FOR XML PATH but couldn't get it to work, here is the query for my attempt:</p>
  
  <pre><code>SELECT 
  p.p_surname,
  p.p_forenames,
  LEFT(sr.sr_weekpattern , LEN(sr.sr_weekpattern )-1) AS Weekpattern
  FROM 
  unitesnapshot.dbo.capd_studentregister sr
  INNER JOIN unitesnapshot.dbo.capd_person p ON p.p_id = sr.sr_student
  CROSS APPLY
  (
      SELECT sr.sr_weekpattern + ' '
      FROM unitesnapshot.dbo.capd_studentregister sr1
      INNER JOIN unitesnapshot.dbo.capd_person p1 ON p1.p_id = sr1.sr_student
  
      WHERE p.p_id = p1.p_id
      FOR XML PATH('')
  ) pre_trimmed (Weekpattern)
  GROUP BY p.p_surname, p.p_forenames, sr.sr_weekpattern;
  </code></pre>
  
  <p>I get this error when running this query:</p>
  
  <p>Invalid length parameter passed to the LEFT or SUBSTRING function.</p>
  
  <p>Any help would be much appreciated</p>