String_agg not working as needed

Question

String_agg not working as needed

DaveBriCam

SSCarpal Tunnel

Points: 4307
More actions
March 23, 2026 at 7:19 pm

#4754453
I'm trying to get this string_agg to put all the 'comments' into one result field like instead of:
433 2018-11-06 11:08:12.793 Customer called
433 2018-11-06 11:08:12.793 Customer left message
This is needed:
433 2018-11-06 11:08:12.793 Customer called, Customer left message
Any ideas?:
```
WITH MaxCommentCTE
AS (
SELECT DISTINCT client_id
,MAX(last_upd_dt) OVER (PARTITION BY client_id) AS LastCommentDate
,string_agg([comments], ', ') within
GROUP (
ORDER BY Comments
) AS Comment
FROM AB.dbo.tbl_reg_waiting
GROUP BY client_id
,last_upd_dt
,comments
)
SELECT *
FROM MaxCommentCTE
```

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale One Orange Chip Points: 25021 More actions · Answer 1

So the reason (I suspect) is that you are using string_agg wrong. I don't have sample data from you to test things, so just guessing here, but I'd start by reading the documentation on that function:

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver17

which states about the "WITHIN GROUP" part of that function:

Optionally specify order of concatenated results using WITHIN GROUP clause

So WITHIN GROUP is just for ordering, you are grouping by client ID, last updated datetime and comments. So if any comment is different, it is outside the group. Since your MAX is already in a windowing function, remove "comments" from your group by and you should get better results. BUT that also assumes that your last_upd_dt is going to be the same for the comment (likely not), so you MAY want to remove that from your group by as well...

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

pietlinden SSC Guru Points: 63645 More actions · Answer 2

Like this?

SELECT c.CustomerID, c.CallDate, STRING_AGG(c.Notes,'|')
FROM (VALUES
(433,    '2018-11-06 11:08:12.793',  'Customer called'),
(433,     '2018-11-06 11:08:12.793',  'Customer left message')
) c(CustomerID, CallDate, Notes)
GROUP BY c.CustomerID, c.CallDate;

Phil Parkin SSC Guru Points: 247230 More actions · Answer 3

The solution proposed above works only if 'Customer called' and 'Customer left message' happened at exactly the same time. As they appear to be two separate rows of data, I'm assuming that, in practice, they wouldn't be.

If so, you'll need to adopt a slightly more sophisticated grouping strategy. But without knowing more about your data and desired results, we'd only be guessing.