Difference between revisions of "SQL - Group several lines in the same column with LIST TAG"
Jump to navigation
Jump to search
Line 7: | Line 7: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
SQL v2 workaround for "too long string column concatenation" : | SQL v2 workaround for "too long string column concatenation issue" : | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> |
Revision as of 12:18, 22 May 2020
SQL :
SELECT country,
listagg(first_name, ', ') WITHIN GROUP (order by first_name)
FROM tablename
GROUP BY country
SQL v2 workaround for "too long string column concatenation issue" :
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);