分组以创建垂直合并 [英] Group by to create a vertical coalesce

问题描述

我有这个查询.

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

结果，如您在此小提琴中所见

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

每笔交易显示多行.我想更改查询，以便每笔交易仅发生1行.

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

这怎么办?

推荐答案

这正是pivot的用途:

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')
);

这篇关于分组以创建垂直合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！