Group by to create a vertical coalesce

Question

I have this query.

select transaction, bbk, sbk, obk, ibk
from
(
  select 
    transaction, 
    case when "'BBK'" = 1 then country end bbk,
    case when "'SBK'" = 1 then country end sbk,
    case when "'OBK'" = 1 then country end obk,
    case when "'IBK'" = 1 then country end ibk  
  from (
    select 
    regexp_substr("col_a", '[^~]+', 1, 1) as transaction,
    regexp_substr("col_a", '[^~]+', 1, 2) as code,
    regexp_substr("col_a", '[^~]+', 1, 3) as country
    from Table1 t)
  pivot 
  ( 
    --case when count(code) = 1 then 
    count(code)
    for code in ('BBK','SBK','OBK','IBK')
  )  
)
group by transaction, bbk, sbk, obk, ibk
order by transaction

The results, as you can see in this fiddle

00004719    US  US  (null)  (null)
00004719    (null)  (null)  GB  (null)
00004719    (null)  (null)  (null)  DE

Show multiple lines per transaction. I would like to alter the query so that only 1 line per transaction occurs.

Essentially coalescing vertically the nulls in the other records to achieve:

00004719    US  US  GB  DE

How might this be done?

Answer 1

That is exactly what pivot is for:

select 
  transaction, 
  "'BBK'",
  "'SBK'",
  "'OBK'",
  "'IBK'"
from (
  select 
  regexp_substr("col_a", '[^~]+', 1, 1) as transaction,
  regexp_substr("col_a", '[^~]+', 1, 2) as code,
  regexp_substr("col_a", '[^~]+', 1, 3) as country
  from Table1 t)
pivot 
( 
  MAX(country) for code in ('BBK','SBK','OBK','IBK')
);