create table days(id, day_name, day_type) as  
select 1, 'Sunday', 'Weekend' from dual union all  
select 2, 'Monday', 'Weekday' from dual union all  
select 3, 'Tuesday', 'Weekday' from dual union all  
select 4, 'Wednesday', 'Weekday' from dual union all  
select 5, 'Thursday', 'Weekday' from dual union all  
select 6, 'Friday', 'Weekday' from dual union all  
select 7, 'Saturday', 'Weekend' from dual 

select * from days

select listagg(day_name,',') within group (order by id) over(partition by day_type) as display, day_type as return from days

select distinct listagg(day_name,',') within group (order by id) over(partition by day_type) as display, day_type as return from days

select distinct listagg(day_name,',')   
       within group (order by id)   
       over(partition by day_type) || '('|| day_type ||')' as display, day_type as return   
from days