I have an Oracle12 and a table

no rows with a% a% and b% b%

I want to select 4 columns: Debit Credit where exists a% and not b% in any column and Debit Credit where exists a% and b% in any column. The first column pair must correspond the second by a% value.

Something like

with t as (
  select 'a1' Debit, 'b1' Credit from dual
  union all select 'c1', 'a1' from dual
  union all select 'c2', 'a1' from dual
  union all select 'b2', 'a2' from dual
  union all select 'a2', 'b3' from dual
  union all select 'a2', 'c2' from dual)
select Debit, Credit, null DebitB, null CreditB
    from t
    where (Debit like 'a%' or Credit like 'a%')
    and (Debit not like 'b%' and Credit not like 'b%')
    union all
    select null, null, Debit, Credit
    from t
    where (Debit like 'a%' or Credit like 'a%')
    and (Debit like 'b%' or Credit like 'b%')

but merge (exclude empty cells if possible) these 6 rows into 4 "grouped" by a%. At first all merged rows with a1, then all merged rows with a2 and so on. Any order within group, nulls last. The result must be

CodePudding user response：

You can number the rows and use a FULL OUTER JOIN matching on the a values and the row number:

SELECT t1.Debit,
       t1.Credit,
       t2.debit  AS DebitB,
       t2.credit AS CreditB
FROM   (
         SELECT t.*,
                ROW_NUMBER() OVER (
                  PARTITION BY CASE WHEN debit LIKE 'a%' THEN debit ELSE credit END
                  ORDER BY CASE WHEN debit LIKE 'a%' THEN credit ELSE debit END
                ) AS rn
         FROM   t
         WHERE  debit LIKE 'a%' AND credit NOT LIKE 'b%'
         OR     debit NOT LIKE 'b%' AND credit LIKE 'a%'
       ) t1
       FULL OUTER JOIN (
         SELECT t.*,
                ROW_NUMBER() OVER (
                  PARTITION BY CASE WHEN debit LIKE 'a%' THEN debit ELSE credit END
                  ORDER BY CASE WHEN debit LIKE 'a%' THEN credit ELSE debit END
                ) AS rn
         FROM   t
         WHERE  debit LIKE 'a%' AND credit LIKE 'b%'
         OR     debit LIKE 'b%' AND credit LIKE 'a%'
       ) t2
       ON    CASE WHEN t1.debit LIKE 'a%' THEN t1.debit ELSE t1.credit END
             = CASE WHEN t2.debit LIKE 'a%' THEN t2.debit ELSE t2.credit END
          AND t1.rn = t2.rn

Which, for the sample data:

CREATE TABLE t (debit, credit) as
  select 'a1', 'b1' from dual union all
  select 'c1', 'a1' from dual union all
  select 'c2', 'a1' from dual union all
  select 'b2', 'a2' from dual union all
  select 'a2', 'b3' from dual union all
  select 'a2', 'c2' from dual;

Outputs:

fiddle