title: SQL STUFF function roll up multiple rows
body: <p>I am trying to combine a field when entries are made on the same day. The user may enter a message multiple times for a given Source on the same day. The resulting table is following - </p>
  
  <p><a href="https://i.stack.imgur.com/zdwql.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/zdwql.png" alt="enter image description here"></a> </p>
  
  <p>What I would like to do is make one entry combining the <code>MessageText</code> for <code>SourceID</code> on the same day.</p>
  
  <p>I have it where it will create an record for the <code>SourceID</code> and the same day however it will place every <code>MessageText</code> for that <code>SourceID</code> no matter what the date is. It does give one row for the same days. For instance the <code>SourceID</code> has 2 entries on 2012-11-08 on 1 on 2017-07-11. It creates a row for 2012-11-08 and one for 2017-07-11 however it places all 3 <code>MessageText</code> in the row.</p>
  
  <p><a href="https://i.stack.imgur.com/Grr6X.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Grr6X.png" alt="enter image description here"></a></p>
  
  <p>My code is - </p>
  
  <pre><code>SELECT distinct  s.SourceID, stuff ( (select ', ' + rtrim(x.MessageText)
                                              from [AVData].[dbo].[LogCentralMessageData] x
                                              inner join AVData.[dbo].[Source] a on a.SourceID = t.SourceID
                                              inner join(select distinct max(m.CreatedOn)over (partition by r.SourceSiteID, Convert(date, m.CreatedOn)) as maxDate, r.SourceSiteID
                                                  from [AVData].[dbo].[LogCentralMessageData] m 
                                                  left join AVData.[dbo].[Source] r on r.SourceID = m.SourceID
                                                  )  t on t.SourceSiteID = a.SourceSiteID and convert(date, t.maxDate) = Convert(date, x.CreatedOn)
  
                                              where x.SourceID = a.SourceID  
  
                                              for XML path('')), 1, 1, '') message_text
          ,convert(date, t.CreatedOn) as CreatedDate
  
  
  from [AVData].[dbo].[LogCentralMessageData] t
  
  left join AVData.[dbo].[Source] s on s.SourceID = t.SourceID
  order by SourceID, CreatedDate
  </code></pre>