Difference between revisions of "SQL - Group several lines in the same column with LIST TAG"

From GUILLARD WIKI
Jump to navigation Jump to search
Line 1: Line 1:
SQL :
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT country,  
SELECT country,  
Line 6: Line 7:
</syntaxhighlight>
</syntaxhighlight>


To avoid to have issue with too long string concatenantion, this is a nice workaround :  
SQL v2 workaround for "too long string concatenation" :


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
with PRECALC  
with PRECALC as (
as (select  
  select floor(4000/(max(length(MY_COLUMN)+LENGTH(',')))) as MAX_FIELD_LENGTH
          floor(4000/(max(length(MY_COLUMN)+LENGTH(',')))) as MAX_FIELD_LENGTH
  from MY_TABLE
      from MY_TABLE)
)


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

Revision as of 12:13, 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 concatenation" : 

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);
Retrieved from "http://thomasguillard.fr/wiki/index.php?title=SQL_-_Group_several_lines_in_the_same_column_with_LIST_TAG&oldid=309"