How to create a unique pairwise list of a column value based on table entry and its referenced entry-CodePudding

Im trying to extract the following information from the oracle table below: A list of all the unique pairwise Status combinations for entries and their referenced entries. Entries with no referenced entry will be ignored. For example, for the entry 10 I expect the output to be (1,3) because its status is 1 and the status of the referenced entry 7 is 3. If the list doesn't already have this combination, it should be added to the list. Can anyone guide me in the right direction? I'm totally clueless as to how to even google what I want to achieve.

EDIT: The first column is the ID of the entry, the second column is the status of the entry, and the third column is the ID of another entry in the same table that is referenced.

CodePudding user response：

Looks like a self join:

Sample data:

SQL> with test (id, status, ref_id) as
  2    (select  1, 0, null from dual union all
  3     select  2, 1, 3    from dual union all
  4     select  3, 3, null from dual union all
  5     select  4, 6, 6    from dual union all
  6     select  5, 0, 1    from dual union all
  7     select  6, 4, null from dual union all
  8     select  7, 3, null from dual union all
  9     select  8, 5, 9    from dual union all
 10     select  9, 2, null from dual union all
 11     select 10, 1, 7    from dual
 12    )

Query:

 13  select a.id, a.status, b.status
 14  from test a join test b on b.id = a.ref_id
 15  where a.ref_id is not null
 16  order by a.id;

        ID     STATUS     STATUS
---------- ---------- ----------
         2          1          3
         4          6          4
         5          0          0
         8          5          2
        10          1          3

SQL>

If you want to get distinct pairs (but still know IDs involved), you could use listagg (it'll work as long as resulting string doesn't exceed 4000 characters; if it does, use xmlagg instead):

 13  select listagg(a.id, ', ') within group (order by a.id) id,
 14         a.status, b.status
 15  from test a join test b on b.id = a.ref_id
 16  where a.ref_id is not null
 17  group by a.status, b.status
 18  order by id;

ID                       STATUS     STATUS
-------------------- ---------- ----------
2, 10                         1          3
4                             6          4
5                             0          0
8                             5          2

SQL>

If you don't care about IDs, then

 13  select distinct a.status, b.status
 14  from test a join test b on b.id = a.ref_id
 15  where a.ref_id is not null
 16  order by a.status, b.status;

    STATUS     STATUS
---------- ----------
         0          0
         1          3
         5          2
         6          4

SQL>