listunagg函数? [英] listunagg function?
本文介绍了listunagg函数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
oracle中是否有类似listunagg函数的东西?例如,如果我有类似这样的数据:
is there such thing in oracle like listunagg function? For example, if I have a data like:
------------------------------------------------------------
| user_id | degree_fi | degree_en | degree_sv |
--------------------------------------------------------------
| 3601464 | 3700 | 1600 | 2200 |
| 1020 | 100 | 0 | 0 |
| 3600520 | 100,3200,400 | 1300, 800, 3000 | 1400, 600, 1500 |
| 3600882 | 0 | 100 | 200 |
--------------------------------------------------------------
我想显示如下数据:
-----------------------------------------------
| user_id | degree_fi | degree_en | degree_sv |
-----------------------------------------------
| 3601464 | 3700 | 1600 | 2200 |
| 1020 | 100 | 0 | 0 |
| 3600520 | 100 | 1300 | 1400 |
| 3600882 | 0 | 100 | 200 |
| 3600520 | 3200 | 800 | 600 |
| 3600520 | 400 | 3000 | 1500 |
-----------------------------------------------
我试图找到一些与listagg相反的功能,但找不到任何功能. 在此先感谢:-)
I tried to find some function like opposite of listagg but couldn't find any. Thanks in advance :-)
推荐答案
正如@be现在已经在注释中指出的,Oracle没有提供这种功能.因此,作为一种快速的解决方法,您可以编写类似的查询:
As @be here now has already noted in the comment Oracle doesn't provide such a function. So as a quick workaround you could write similar query:
with t1(user_id, degree_fi, degree_en, degree_sv) as
(
select 3601464, '3700', '1600', '2200' from dual union all
select 1020 , '100' , '0' , '0' from dual union all
select 3600520, '100,3200,400', '1300, 800, 3000', '1400, 600, 1500' from dual union all
select 3600882, '0', '100', '200' from dual
),
Occurence(ocr) as(
select Level as ocr
from (select max(greatest(regexp_count(degree_fi, '[^,]+')
, regexp_count(degree_en, '[^,]+')
, regexp_count(degree_sv, '[^,]+')
)
) mx
from t1
)
connect by level <= mx
)
select *
from (
select User_id
, regexp_substr(degree_fi, '[^,]+', 1, o.ocr) as degree_fi
, regexp_substr(degree_en, '[^,]+', 1, o.ocr) as degree_en
, regexp_substr(degree_sv, '[^,]+', 1, o.ocr) as degree_sv
from t1 t
cross join Occurence o
)
where degree_fi is not null
or degree_en is not null
or degree_sv is not null
结果:
User_Id Degree_Fi Degree_En Degree_Sv
------------------------------------------------------------
3601464 3700 1600 2200
1020 100 0 0
3600520 100 1300 1400
3600882 0 100 200
3600520 3200 800 600
3600520 400 3000 1500
这篇关于listunagg函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文