Concatenate the rows

You have table named SHOW_TABLE having 2 columns SHOW_ID and EMP_NAME having data as follows:

SHOW_ID           EMP_NAME
   1                      Gaurav
   2                      Kalpana
   1                      Ashok
   2                      Manish

Write a query to return data as follows:

SHOW_ID                EMP_NAME
     1                     Gaurav,Ashok
     2                     Kalpana, Manish


Solution:

select show_id,rn, ltrim(sys_connect_by_path (emp_name,','),',') concatenated
from
(
select
show_id,
emp_name,
row_number() over (partition by show_id order by show_id )rn,
count(*) over (partition by show_id) cnt from
(
select distinct show_id, emp_name from show_table
))
where
rn=cnt
start with rn=1
CONNECT BY prior show_id=show_id and PRIOR rn = rn -1
order by show_id,length(concatenated) desc