Geek Answers Handbook

Listunagg function?

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

------------------------------------------------------------ | 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 would like to show the data as follows:

 ----------------------------------------------- | 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 similar to the opposite of listagg, but could not find it. Thanks in advance: -)

+6
source share
2 answers

As it is now, Oracle's comment already mentions that Oracle does not provide such a function. So, as a quick workaround, you can write a 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
+7
source

To expand the list, think about what Tom has to say to Oracle “Ask Tom,” see http://www.oracle.com/technetwork/issue-archive/2007/07-mar/o27asktom-084983.html Listing 3 or 4 .

My preferred option, which Tom does not discuss, is good for short lines (<34 characters). I am using 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;
0
source

More articles:


All Articles