hello,

I am new to writing this kind of SQL and I am almost there with this statement but not quite.

I'm trying to write a query using listagg and I am getting repeating values in the requirement column when I have 2 passengers. This is because each passenger can have multiple requirements. So I am getting all the requirements in this column.

What I need is it to only give me distinct values. Is this possible?



select s.run_id, run_refference, s.day, s.pickuplocation, s.arrival_time, s.dropofflocation, s.collection_time, contractlength, count(distinct p.serviceuser_id) as "Passengers",
listagg(requirement,' : ')within group(order by a.requirement_id) as "Requirements"
from tran_run r, tran_schedule s, tran_serviceuserrequirement q, tran_passengers p, tran_requirement a
where s.run_id= r.run_id
and p.serviceuser_id = q.serviceuser_id
and r.run_id = p.run_id
and q.requirement_id = a.requirement_id
group by s.run_id, run_refference, s.day, s.pickuplocation, s.arrival_time, s.dropofflocation, s.collection_time, contractlength
order by s.run_id, day



Sample out put:

RUN_REF DAY A_TIME C_TIME Passengers Requirements
ED18 Friday 08:50 16:00 2 Escort : Escort : Travel Alone
ED18 Monday 09:00 15:00 2 Escort : Escort : Travel Alone
ED18 Thursday 09:00 15:30 2 Escort : Escort : Travel Alone
ED18 Tuesday 09:00 15:00 2 Escort : Escort : Travel Alone
ED22 Friday 08:50 15:05 1 Escort : Travel Alone
ED22 Monday 08:50 15:55 1 Escort : Travel Alone
ED22 Thursday 08:50 15:05 1 Escort : Travel Alone
ED22 Tuesday 08:50 15:05 1 Escort : Travel Alone
ED22 Wednesday08:50 15:55 1 Escort : Travel Alone
RES23 Friday 10:00 15:00 1 Escort
RES23 Monday 10:00 15:00 1 Escort
RES23 Thursday 10:00 15:00 1 Escort
RES23 Tuesday 10:00 15:00 1 Escort
RES23 Wednesday10:00 15:00 1 Escort


Thank you