title: Group by to create a vertical coalesce
body: <p>I have this query.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The results, as you can see in <a href="http://sqlfiddle.com/#!4/caf32/73" rel="nofollow">this fiddle</a></p>
  
  <pre><code>00004719    US  US  (null)  (null)
  00004719    (null)  (null)  GB  (null)
  00004719    (null)  (null)  (null)  DE
  </code></pre>
  
  <p>Show multiple lines per transaction.  I would like to alter the query so that only 1 line per transaction occurs.</p>
  
  <p>Essentially coalescing vertically the nulls in the other records to achieve:</p>
  
  <pre><code>00004719    US  US  GB  DE
  </code></pre>
  
  <p>How might this be done?</p>