How do I select different values when using multiple LISTAGG () functions in a select clause?
I am trying to get race_code, chara_code and reason_code as a list using the following query:
SELECT a.pid,
LISTAGG(a.rc, ',') WITHIN GROUP (ORDER BY a.rc) AS race,
LISTAGG(a.cc, ',') WITHIN GROUP (ORDER BY a.cc) as chara_codes,
LISTAGG(a.rrc, ',') WITHIN GROUP (ORDER BY a.rrc) AS removal_reason
FROM (
SELECT UNIQUE
p.person_id pid,
r.race_code rc,
c.characteristic_code cc,
rr.removal_reason_code rrc
FROM person p left outer join race r on p.person_id = r.person_id
left outer join characteristic c on p.person_id = c.person_id
left outer join placement_episode pe on p.person_id = pe.child_id
left outer join removal_reason rr on pe.placement_episode_id = rr.placement_episode_id
) a
GROUP BY a.pid
I tried this request after linking to some links like link1 and link2 . But after that I also cannot get unique values for all fields.
My o / p fits like:
pid race_code chara_code reason_code
1 a,b,b,c c1,c1,c2,c3 r1,r2,r3,r3
2 a,c,d,d,d c1,c2,c2 r3,r3
and so on.
If I try to get only one field at a time while keeping the necessary links, then it gives the correct result. But for several LISTAGG () functions, these are duplicate values.
I have no way to do this. Is there any other way that I can get different values?
source to share
This, unfortunately, is more difficult than necessary. But you can do it. The idea is to list each of the values and then use them case
to pass the argument NULL
to LISTAGG()
.
SELECT a.pid,
LISTAGG(CASE WHEN rc_seqnum = 1 THEN a.rc END, ',') WITHIN GROUP (ORDER BY a.rc) AS race,
LISTAGG(CASE WHEN cc_seqnum = 1 THEN a.cc END, ',') WITHIN GROUP (ORDER BY a.cc) as chara_codes,
LISTAGG(CASE WHEN rrc_seqnum = 1 THEN a.rrc END, ',') WITHIN GROUP (ORDER BY a.rrc) AS removal_reason
FROM (SELECT p.person_id as pid, r.race_code as rc, c.characteristic_code as cc,
rr.removal_reason_code as rrc,
row_number() over (partition by p.person_id, r.race_code order by r.race_code) as rc_seqnum,
row_number() over (partition by p.person_id, c.characteristic_code order by c.characteristic_code) as cc_seqnum,
row_number() over (partition by p.person_id, rr.removal_reason_code order by rr.removal_reason_code) as rrc_seqnum
FROM person p left outer join race r on p.person_id = r.person_id
left outer join characteristic c on p.person_id = c.person_id
left outer join placement_episode pe on p.person_id = pe.child_id
left outer join removal_reason rr on pe.placement_episode_id = rr.placement_episode_id
) a
GROUP BY a.pid;
The query is an enumeration of strings based on each person and combination of fields. The first time a value is visible, it is set to "1", subsequent values increase gradually. LISTAGG()
only selects the first value.
You should learn about analytic functions. They are very helpful.
source to share
You can use scalar subqueries like in this example:
select p.person_id
, (select LISTAGG(rc, ',') WITHIN GROUP (ORDER BY rc)
from race r where r.person_id = p.person_id
group by r.person_id) race
, (select LISTAGG(cc, ',') WITHIN GROUP (ORDER BY cc)
from characteristic r where r.person_id = p.person_id
group by r.person_id) chara_codes
, (select LISTAGG(rrc, ',') WITHIN GROUP (ORDER BY rrc)
from removal_reason r where r.person_id = p.person_id
group by r.person_id) removal_reason
from person p;
Or you can pre-copy the lists as in this example:
with race_list as (
select person_id
, LISTAGG(rc, ',') WITHIN GROUP (ORDER BY rc) race
from race
group by person_id
), characteristic_list as (
select person_id
, LISTAGG(cc, ',') WITHIN GROUP (ORDER BY cc) chara_codes
from characteristic
group by person_id
), removal_reason_list as (
select person_id
, LISTAGG(rrc, ',') WITHIN GROUP (ORDER BY rrc) removal_reason
from removal_reason
group by person_id
)
select p.person_id
, r.race
, c.chara_codes
, rr.removal_reason
from person p
join race_list r
on r.person_id = p.person_id
join characteristic_list c
on c.person_id = p.person_id
join removal_reason_list rr
on rr.person_id = p.person_id;
source to share