… and that surely is for good reason when you check how the nvarchar(max)/varchar(max) family is treated.
As I moved from PostgreSQL to SQL Server 2008R2 one of the things I missed the most were all the out of the box aggregation functions. And one of them was string_agg().
We migrated last year from 2008R2 to 2017 and I was more than happy to get the function back in my toolkit. Or so I though.
A part of our major ETL process is based on cursor. And I knew the string_agg could help us in simplifying and reducing the procedure definition. So here I go happily and …
Msg 9829, Level 16, State 1, Line 3
STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation
Your dear SSMS 🙂
What a disappointment. In PostgreSQL I never reached such a restriction. I looked for solutions but was kind of left with no success.
Recently I gave it a try again. For so called ICDO codes, we get a main text, and many synonyms. I wanted to get the many synonyms in the last column for some report. And again… the Msg 9829.
However this time the ‘LOB’ caught my eyes. I had been looking for the impact of NVARCHAR(max) on storage and performance, and I could now make the relation:
string_agg(cast(synonym to nvarchar(max)))
Still waiting for GREATEST()/LEAST() function, but we have at least (pun intended) one funky solution (not a fan of the STUFF or PIVOT variants)
select
(select max(tomax)
from (values (col1),
(col2),
(col3))
as x(tomax)) as greatest
from yourtable
Reconsider your assumption and have a nice day!
drop table if exists #sometext;
with t1 as (select * from (values ('some'), ('text')) as x(txt)),
t2 as (select x.txt from t1 join t1 as x on 1=1),
t3 as (select x.txt from t2 join t2 as x on 1=1),
t4 as (select x.txt from t3 join t3 as x on 1=1),
t5 as (select x.txt from t4 join t4 as x on 1=1)
select cast(txt as varchar(200)) as txt
into #sometext
from t5;
print 'let''s fail';
select STRING_AGG(txt, ', ') from #sometext; -- let's fail
print 'let''s go';
select STRING_AGG(cast(txt as varchar(max)), ', ') from #sometext; -- let's go