20

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Eren G.
  • 455
  • 1
  • 5
  • 14
  • 3
    you need to convert it see https://www.mssqltips.com/sqlservertutorial/9371/sql-string-agg-function/#:%7E:text=Error%20message%3A%20STRING_AGG%20aggregation%20result,varchar(max)%20as%20follows. – nbk Sep 19 '22 at 11:58
  • 2
    The error message seems pretty clear about what you need to do. What exactly is the problem? – Charlieface Sep 19 '22 at 11:58
  • 1
    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). – Thorsten Kettner Sep 19 '22 at 12:00

2 Answers2

35

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.

Baris Erden
  • 368
  • 3
  • 5
  • 6
    If you add a sentence or two about why this works this will be an even better answer. – idstam Sep 19 '22 at 12:03
  • Sorry about that, I was busy and had to be quick. Won't happen again – Baris Erden Sep 19 '22 at 12:06
  • 5
    @idstam 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](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver16#return-types) – Adilet Soronov Jan 12 '23 at 03:51
0

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;
SelVazi
  • 10,028
  • 2
  • 13
  • 29
user21752862
  • 1