SQL - Group several lines in the same column with LIST TAG

From GUILLARD WIKI
Revision as of 11:08, 22 May 2020 by Guillard (talk | contribs)
Jump to navigation Jump to search
SELECT country, 
listagg(first_name, ', ') WITHIN GROUP (order by first_name) 
FROM tablename 
GROUP BY country

To avoid to have issue with too long string concatenantion, this is a nice workaround :

with PRECALC 
as (select 
           floor(4000/(max(length(MY_COLUMN)+LENGTH(',')))) as MAX_FIELD_LENGTH
       from MY_TABLE)

select LISTAGG(MY_COLUMN,',') 
WITHIN GROUP(ORDER BY floor(rownum/MAX_FIELD_LENGTH), MY_COLUMN)
from MY_TABLE, PRECALC
group by floor(rownum/MAX_FIELD_LENGTH);