CREATE TABLE source_det (
e_id NUMBER(10),
sys_name VARCHAR2(20),
ref_id NUMBER(10),
sys_other VARCHAR2(30)
);
INSERT INTO source_det VALUES(11,'SOURCE',992,null);
INSERT INTO source_det VALUES(11,'SOURCE',637,null);
INSERT INTO source_det VALUES(11,'SOURCE',null,'Manual');
INSERT INTO source_det VALUES(11,'TARGET',637,null);
commit;
My Attempt:
select e_id,listagg(source,';')source,listagg(target,';')target,source_other FROM (select e_id,
CASE when sys_name = 'SOURCE' THEN ref_id END source ,
CASE when sys_name = 'TARGET' THEN ref_id END target,
CASE when sys_name = 'SOURCE' AND ref_id IS NULL THEN sys_other END source_other
from source_det
GROUP BY e_id);
From the above set of data I need only a single row to be returned from the SELECT query but I am getting null values instead along with the required values. If sys_name is SOURCE then the result should be ref_id. If there is more than one record then it should be spilitted by delimiter ; If sys_name is TARGET then result should be ref_id If sys_name is SOURCE AND ref_id is null then it should give sys_other as a result. But from my query I am not getting a single row instead getting 4 rows. But ideally, I need only one row with e_id 11 And most important is in place of null values it could be possible that there would be () present in place of null. So, need to exclude this as well. After getting the desired result then we can use DECODE(ref_id,'()',null) like this.
Expected result:
+------+---------+--------+--------------+
| e_id | source | target | source_other |
+------+---------+--------+--------------+
| 11 | 992;637 | 637 | Manual |
+------+---------+--------+--------------+
Tool used: SQL Developer (18c)