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.

Sherry

you need to convert it see mssqltips.com/sqlservertutorial/9371/sql-string-agg-function/…. – Demotic

The error message seems pretty clear about what you need to do. What exactly is the problem? – Balmung

It is likely you want a distinct list of oberbereich, but you are getting duplicates. In that case, get the distinct values in a subquery, because SQL Server's STRING_AGG does not accept the DISTINCT keyword (which I consider a design flaw). – Ferryboat

@Balmung lol wasn't even remotely clear in the slightest to me how to fix it! Especially since the field I was aggregating is an int. – Chee

@Chee The error message says clearly that the result is too long. And quite clear in the documentation what the return type is learn.microsoft.com/en-us/sql/t-sql/functions/… – Balmung

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.

Licence

If you add a sentence or two about why this works this will be an even better answer. – Amygdalate

Sorry about that, I was busy and had to be quick. Won't happen again – Licence

@Amygdalate if the argument is NVARCHAR(1...4000) then the return type is NVARCHAR(4000). Same with NVARCHAR(1...8000) -> NVARCHAR(8000), NVARCHAR(MAX) -> NVARCHAR(MAX) I hope it helps: STRING_AGG – Burnisher

Answer 2

Baris Erden Thanku soo much for the help.. it worked for me

SELECT Employee_Name, STRING_AGG( CAST(Project_Name AS VARCHAR(MAX)), ',') as all_project_names
FROM [10.180.0.63].ware_dw.dbo.employee_360
group by Employee_Name;

Nuclei