转置和汇总Oracle列数据 [英] Transposing and aggregating Oracle column data

问题描述

我有以下数据

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  

演示

这篇关于转置和汇总Oracle列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！