列表功能?

2021-12-30 00:00:00 csv string tokenize oracle11g oracle

oracle 中是否有类似 listunagg 函数的东西?例如，如果我有这样的数据:

is there such thing in oracle like listunagg function? For example, if I have a data like:

<头>
user_iddegree_fidegree_endegree_sv
3601464370016002200
102010000
3600520100,3200,4001300、800、30001400、600、1500
36008820100200

我想显示这样的数据:

<头>
user_iddegree_fidegree_endegree_sv
3601464370016002200
102010000
360052010013001400
36008820100200
36005203200800600
360052040030001500

我试图找到一些与 listagg 相反的函数，但找不到.

I tried to find some function like opposite of listagg but couldn't find any.

推荐答案

正如@be here 现在已经在评论中指出的，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

相关文章