分组以创建垂直合并

本文介绍了分组以创建垂直合并的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这个查询.

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