Home > OS >  Oracle SQL merge half empty query lines
Oracle SQL merge half empty query lines

Time:01-20

I have an Oracle12 and a table

Debit Credit
a1 b1
c1 a1
c2 a1
b2 a2
a2 b3
a2 c2

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

Debit Credit DebitB CreditB
c1 a1 a1 b1
c2 a1
a2 c2 b2 a2
a2 b3

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:

DEBIT CREDIT DEBITB CREDITB
c1 a1 a1 b1
a2 c2 b2 a2
null null a2 b3
c2 a1 null null

fiddle

  • Related