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

From GUILLARD WIKI
Jump to navigation Jump to search
(Created page with "<syntaxhighlight lang="sql"> select ost_pid_facility, listagg( ('COL: '||trim(mic_txt_mis_value)||' OST: '||trim(ost_nme_alias)) , ', ' ) WITHIN GROUP (ORDER BY ost_pid_f...")
 
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
SQL :
<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SELECT country,
listagg(first_name, ', ') WITHIN GROUP (order by first_name)
FROM tablename
GROUP BY country
</syntaxhighlight>


select ost_pid_facility,
SQL v2 workaround for "too long string column concatenation issue" :
listagg(
 
  ('COL: '||trim(mic_txt_mis_value)||' OST: '||trim(ost_nme_alias)) ,
<syntaxhighlight lang="sql">
  ', '
with PRECALC as (
  select floor(4000/(max(length(MY_COLUMN)+LENGTH(',')))) as MAX_FIELD_LENGTH
  from MY_TABLE
)
)
WITHIN GROUP (ORDER BY ost_pid_facility DESC) "COL OST"
 
from vls_mis_code
select
inner join vls_fam_global1 on gb1_cde_code = mic_cde_mis_type
    LISTAGG(MY_COLUMN,',') WITHIN GROUP(ORDER BY floor(rownum/MAX_FIELD_LENGTH), MY_COLUMN)
inner join vls_outstanding on ost_rid_outstandng = mic_pid_product_id
from MY_TABLE, PRECALC
where mic_cde_mis_type = 'NGCOL' and mic_cde_prod_type = 'LOAN'
group by floor(rownum/MAX_FIELD_LENGTH);
group by ost_pid_facility
 
-- OR
 
select listagg(product_name, ',' on overflow truncate) within group (order by product_name)
from products;


</syntaxhighlight>
</syntaxhighlight>

Latest revision as of 15:53, 8 October 2021

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);

-- OR 

select listagg(product_name, ',' on overflow truncate) within group (order by product_name)
from products;
Retrieved from "http://thomasguillard.fr/wiki/index.php?title=SQL_-_Group_several_lines_in_the_same_column_with_LIST_TAG&oldid=319"