转置和聚合 Oracle 列数据

2021-12-30 00:00:00 sql oracle11g oracle listagg oracle11gr2

我有以下数据

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

演示

相关文章