I needed to collapse multiple rows into a single row — the circuits within a diversity set are stored within the ds_dvrsty_set_circuit table as individual rows & the ds_dvrsty_set_id links the multiple rows. What I wanted was a set ID, set name, and the list of circuits within the set.
To accomplish this, I found LISTAGG which is a little bit like STUFF in MSSQL. This query produces a single row for each diversity set that contains the set ID, the set name, and a comma delimited list of set members.
SELECT
ds_dvrsty_set_circuit.ds_dvrsty_set_id,
(select ds_dvrsty_set.ds_dvrsty_set_nm from ds_dvrsty_set where ds_dvrsty_set_id = ds_dvrsty_set_circuit.ds_dvrsty_set_id) as set_name,
LISTAGG(ds_dvrsty_set_circuit.circuit_design_id, ',') WITHIN GROUP(ORDER BY ds_dvrsty_set_circuit.ds_dvrsty_set_id) AS member_circuits
FROM
ds_dvrsty_set_circuit
left outer join ds_dvrsty_set on ds_dvrsty_set.ds_dvrsty_set_id = ds_dvrsty_set_circuit.DS_DVRSTY_SET_ID
WHERE
ds_dvrsty_set_circuit.ds_dvrsty_set_id in (select distinct ds_dvrsty_set_id from ds_dvrsty_set_circuit where circuit_design_id in (14445678, 5078901) )
AND
ds_dvrsty_set.ds_dvrsty_set_nm like '43%'
GROUP BY
ds_dvrsty_set_circuit.ds_dvrsty_set_id
ORDER BY
ds_dvrsty_set_circuit.ds_dvrsty_set_id;
Voila — exactly what I needed. If the searched circuit design IDs appear in more than one set, there is a new row for each set ID.
