listunagg function?

Question

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

and I'd like to show data like this:

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

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

Hymie

no, there is no such native function. only custom workarounds – Sycee

possible duplicate of how to convert csv to table in oracle – Bryan

Answer 1

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

Result:

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 

Wader

Answer 2

There is listunagg function provided by OraOpenSource Utils package set. It also works well.

Tabulator

Answer 3

To unagg a list consider what Tom has to say at Oracle's "Ask Tom" see https://blogs.oracle.com/oraclemagazine/on-cursors-sql-and-analytics Code Listing 3 or 4.

My preferred option that Tom doesn't discuss is good for short strings (<34 characters). I use the Oracle DBMS_UTILITY.comma_to_table function. Example:

SET SERVEROUTPUT ON
DECLARE
/** test data **/   
  L_LIST1   VARCHAR2(500) := '"A","B","C","Pierre - Andre","D","E","OFVampFVapos;CBryan","F","G","H","I","J"';
  l_list2   VARCHAR2(500);
  l_tablen  BINARY_INTEGER;
  l_tab     DBMS_UTILITY.uncl_array;
BEGIN
  DBMS_OUTPUT.put_line('l_list1 : ' || l_list1);

  DBMS_UTILITY.comma_to_table (
     list   => l_list1,
     tablen => l_tablen,
     tab    => l_tab);

  FOR i IN 1 .. l_tablen LOOP
    DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
  END LOOP;

  DBMS_UTILITY.table_to_comma (
     tab    => l_tab,
     tablen => l_tablen,
     list   => l_list2);

  DBMS_OUTPUT.put_line('l_list2 : ' || l_list2);
end;

Bernardinabernardine

Please note that DBMS_UTILITY.comma_to_table() has the severe limitation of only working with tokens that are valid table names. So can't be longer than 30 chars, can't contain (most) special characters, can't start with a number, etc. That last point rules it out for this question. – Bryan