wmsys.wm_concat()
with t as (
select 'a' x,1 y from dual union all
select 'a',2 from dual union all
select 'b',3 from dual union all
select 'b',4 from dual union all
select 'b',5 from dual )
select x,wmsys.wm_concat(y) from t group by x;
----------------------------------------------------------------------
a --（展开内容:1,2）
b --（展开内容:3,4,5）
wmsys.wm_concat() 返回结果为CLOB类型
listagg() within group()
with t as (
select 'a' x,1 y from dual union all
select 'a',2 from dual union all
select 'b',3 from dual union all
select 'b',4 from dual union all
select 'b',5 from dual )
select x,listagg(y,',') within group(order by y) from t group by x;
-------------------------------------------------------------------------------------------------
a 1,2
b 3,4,5
with t as (
select 'a' x,1 y from dual union all
select 'a',2 from dual union all
select 'b',3 from dual union all
select 'b',4 from dual union all
select 'b',5 from dual )
select distinct x,listagg(y,',') within group(order by y) over(partition by x) from t;
-------------------------------------------------------------------------------------------------
a 1,2
b 3,4,5
with t as (
select 'a' x,1 y from dual union all
select 'a',2 from dual union all
select 'b',3 from dual union all
select 'b',4 from dual union all
select 'b',5 from dual )
select x,wmsys.wm_concat(y) from t group by x;
----------------------------------------------------------------------
a
b
wmsys.wm_concat() 返回结果为CLOB类型
listagg() within group()
with t as (
select 'a' x,1 y from dual union all
select 'a',2 from dual union all
select 'b',3 from dual union all
select 'b',4 from dual union all
select 'b',5 from dual )
select x,listagg(y,',') within group(order by y) from t group by x;
-------------------------------------------------------------------------------------------------
a 1,2
b 3,4,5
with t as (
select 'a' x,1 y from dual union all
select 'a',2 from dual union all
select 'b',3 from dual union all
select 'b',4 from dual union all
select 'b',5 from dual )
select distinct x,listagg(y,',') within group(order by y) over(partition by x) from t;
-------------------------------------------------------------------------------------------------
a 1,2
b 3,4,5
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21998432/viewspace-1227229/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
分析函数简单用法举例
请登录后发表评论
登录
全部评论
