转置和汇总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()窗口分析功能两次用作
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