STRING_AGG aggregation result exceeded the limit of 8000 bytes error

Question

I need to combine texts by group. I found a function called STRING_AGG.

select c.id
, c.bereichsname
, STRING_AGG(j.oberbereich,',') oberBereiches 
from stellenangebote_archiv as j
join bereiche as c on j.bereich_id = c.id
group by c.id, c.bereichsname

But I am getting the following error:

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

Answer 1

Try as below

select c.id
, c.bereichsname
, STRING_AGG( CAST(j.oberbereich as nvarchar(MAX)),',') oberBereiches 
from stellenangebote_archiv j
join bereiche c on j.bereich_id = c.id
group by c.id, c.bereichsname

So the problem is the length of the concatenated string is exceeding the character limit of the result column.

So we are setting the limit to max by converting all values to "nvarchar(max)" to solve the problem.

And "STRING_AGG()" function returns what it gets.