oracle 列表功能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13189575/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
listunagg function?
提问by Jaanna
is there such thing in oracle like listunagg function? For example, if I have a data like:
oracle中是否有像listunagg函数这样的东西？例如，如果我有这样的数据：
------------------------------------------------------------
| 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. Thanks in advance :-)
我试图找到一些类似于 listagg 的功能，但找不到任何功能。提前致谢 ：-）
采纳答案by Nick Krasnov
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:
正如@be 现在已经在评论中指出的，Oracle 没有提供这样的功能。因此，作为一种快速解决方法，您可以编写类似的查询：
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
回答by ZZa
There is listunaggfunctionprovided by OraOpenSource Utilspackage set. It also works well.
有listunagg功能提供OraOpenSource utils的包集合。它也运作良好。
回答by Fred N. Vogt
To unagg a list consider what Tom has to say at Oracle's "Ask Tom" see http://www.oracle.com/technetwork/issue-archive/2007/07-mar/o27asktom-084983.htmlCode Listing 3 or 4.
要解开列表，请考虑 Tom 在 Oracle 的“Ask Tom”中所说的话，请参阅http://www.oracle.com/technetwork/issue-archive/2007/07-mar/o27asktom-084983.html代码清单 3 或 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:
Tom 没有讨论的我的首选选项适用于短字符串（<34 个字符）。我使用 Oracle DBMS_UTILITY.comma_to_table 函数。例子：
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;
