Difference between revisions of "SQL - Group several lines in the same column with LIST TAG"
Jump to navigation
Jump to search
Line 4: | Line 4: | ||
FROM tablename | FROM tablename | ||
GROUP BY country | GROUP BY country | ||
</syntaxhighlight> | |||
To avoid to have issue with too long string concatenantion, this is a nice workaround : | |||
<syntaxhighlight lang="sql"> | |||
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); | |||
</syntaxhighlight> | </syntaxhighlight> |
Revision as of 12:08, 22 May 2020
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);