Convert to varchar max inside string_agg function

Question

I have a function a STRING_AGG function which is throwing error

Error message: STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

As this Documentation, says I need to convert to varchar(max)

Current:

 STRING_AGG(CONCAT([CT].[DeviceType] , ': ' , [COM].[Address]) , ', ')) AS [Contact]

Try:

STRING_AGG(CONVERT(VARCHAR(MAX),(CONCAT([CT].[DeviceType] , ': ' , [COM].[Address])) , ', ')) AS [Contact]

But it is throwing error:

The STRING_AGG function requires 2 argument(s).

What am I doing wrong? Regards

Answer 1

You have put the separator within the concat, it should be the second parameter:

STRING_AGG(CONVERT(VARCHAR(MAX),(CONCAT([CT].[DeviceType] , ': ' , [COM].[Address])) ), ', ') AS [Contact]