本文介绍了转置和聚合 Oracle 列数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下数据
Base End
RMSA Item 1
RMSA Item 2
RMSA Item 3
RMSB Item 1
RMSB Item 2
RMSC Item 4
我想把它转换成下面的格式
I want to convert it to the following format
Key Products
RMSA;RMSB Item 1, Item 2
RMSA Item 3
RMSC Item 4
基本上，那些具有相似结果的应该归为 1 行.但是，由于我在两列上分组，因此我似乎无法使用 listagg 等使其工作.
Basically, those with similar results should be grouped into 1 line. However, I can't seem to get it to work using listagg, etc since I'm grouping on two columns.
有没有办法通过直接的 Oracle 查询来做到这一点?
Is there any way to do this with a direct Oracle query?
推荐答案
你可以使用listagg()窗口解析函数twice作为
You can use listagg() window analytic function twice as
with t1( Base, End ) as
(
select 'RMSA','Item 1' from dual union all
select 'RMSA','Item 2' from dual union all
select 'RMSA','Item 3' from dual union all
select 'RMSB','Item 1' from dual union all
select 'RMSB','Item 2' from dual union all
select 'RMSC','Item 4' from dual
),
t2 as
(
select
listagg(base,';') within group (order by end)
as key,
end
from t1
group by end
)
select key,
listagg(end,',') within group (order by end)
as Products
from t2
group by key
order by products;
Key Products
--------- --------------
RMSA;RMSB Item 1, Item 2
RMSA Item 3
RMSC Item 4
演示
这篇关于转置和聚合 Oracle 列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！