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 -
What I would like to do is make one entry combining the
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.
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...
SELECT st1.SourceID, CAST(st1.CreatedOn AS DATE) message_text = STUFF( (SELECT CONCAT(', ', st2.MessageText) FROM dbo.SomeTable st2 WHERE st1.SourceID = st2.SourceID AND CAST(st1.CreatedOn AS DATE) = CAST(st2.CreatedOn AS DATE) ORDER BY st2.CtreatedOn FOR XML PATH ('') ), 1, 2, '') FROM dbo.SomeTable st1 GROUP BY st1.SourceID, CAST(st1.CreatedOn AS DATE);