SQL STUFF function roll up multiple rows

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 -

enter image description here

What I would like to do is make one entry combining the MessageText for SourceID on the same day.

I have it where it will create an record for the SourceID and the same day however it will place every MessageText for that SourceID no matter what the date is. It does give one row for the same days. For instance the SourceID 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 MessageText in the row.

enter image description here

My code is -

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


  • The following should do the trick...

        CAST(st1.CreatedOn AS DATE)
        message_text = STUFF(
                        CONCAT(', ', st2.MessageText)
                        dbo.SomeTable st2
                        st1.SourceID = st2.SourceID
                        AND CAST(st1.CreatedOn AS DATE) = CAST(st2.CreatedOn AS DATE)
                    ORDER BY 
                    FOR XML PATH ('')
                    ), 1, 2, '')
        dbo.SomeTable st1
        CAST(st1.CreatedOn AS DATE);