Oracle Database 11g 企业版 11.2.0.4.0 版中的 STRAGG - 64 位生产
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37340183/
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
STRAGG in Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
提问by Nu?ito de la Calzada
I want to use STRAGG function in this env.: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, PL/SQL Release 11.2.0.4.0 - Production, CORE 11.2.0.4.0 Production, TNS for Linux: Version 11.2.0.4.0 - Production, NLSRTL Version 11.2.0.4.0 - Production:
我想在这个环境中使用 STRAGG 函数：Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production、PL/SQL Release 11.2.0.4.0 - Production、CORE 11.2.0.4.0 Production、TNS for Linux：版本 11.2.0.4.0 - 生产，NLSRTL 版本 11.2.0.4.0 - 生产：
I have a view called V_USER_ROLE_DESC
我有一个名为V_USER_ROLE_DESC的视图
LOGIN DESC
joanet BS in ANDROID.C.3
joanet DOB in ANDROID.C.3
joanet DO in ANDROID.C.3
joanet BS in ANDROID.C.4
joanet UA in ANDROID.C.4
joanet OV in ANDROID.C.4
joanet OI in ANDROID.C.4
joanet DO in ANDROID.C.4
joanet DHoU in ANDROID.C.4
joanet AOP in ANDROID.C.4
Executin this query
执行这个查询
select
login ,
sys.STRAGG( UNIT_ROLE_DESC || ' - ' ) as string
from
V_USER_ROLE_DESC
where login = 'joanet'
group by
login
;
This is the result:
这是结果：
joanet BS in ANDROID.C.3 - DOB in ANDROID.C.3 - DO in ANDROID.C.3 - BS in ANDROID.C.4 - UA in ANDROID.C.4 - OV in ANDROID.C.4 - OI in ANDROID.C.4 - DO in ANDROID.C.4 - DHoU in ANDROID.C.4 - AOP in ANDROID.C.4 -
But without the where clause ...
但是没有 where 子句......
select
login ,
sys.STRAGG( UNIT_ROLE_DESC || ' - ' ) as string
from
V_USER_ROLE_DESC
--where login = 'joanet'
group by
login
;
this is the result:
这是结果：
...
colau DOB in ANDROID.D.2 -
joanet DOB in ANDROID.D.2 -
sisa DOB in ANDROID.D.2 -
...
Using
使用
SELECT login ,listagg(unit_role_desc,' - ') WITHIN GROUP (ORDER BY unit_role_desc) AS STRING FROM v_user_role_desc WHERE 1=1 GROUP BY LOGIN;
I have this error
我有这个错误
ORA-01489: result of string concatenation is too long
ORA-01489: 字符串连接的结果太长
回答by Andris Krauze
I would suggest using LISTAGG instead of STRAGG. LISTAGG is superior because it allows to specify order of aggregated items.
我建议使用 LISTAGG 而不是 STRAGG。LISTAGG 是优越的，因为它允许指定聚合项目的顺序。
SELECT login
,listagg(unit_role_desc,' - ') WITHIN GROUP (ORDER BY unit_role_desc) AS STRING
FROM v_user_role_desc
WHERE 1=1
-- AND login = 'joanet'
GROUP BY LOGIN;
See also a working example:
另见一个工作示例：
WITH
v_user_role_desc AS (
SELECT 'sisa' login
,'BS in ANDROID.C.3' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'sisa' login
,NULL UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'BS in ANDROID.C.3' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'DOB in ANDROID.C.3' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'DO in ANDROID.C.3' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'BS in ANDROID.C.4' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'UA in ANDROID.C.4' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'OV in ANDROID.C.4' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'OI in ANDROID.C.4' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'DO in ANDROID.C.4' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'DHoU in ANDROID.C.4' UNIT_ROLE_DESC
FROM dual
UNION ALL
SELECT 'joanet' login
,'AOP in ANDROID.C.4' UNIT_ROLE_DESC
FROM dual)
SELECT login
,listagg(unit_role_desc,' - ') WITHIN GROUP (ORDER BY unit_role_desc) AS STRING
FROM v_user_role_desc
WHERE 1=1
-- AND login = 'joanet'
GROUP BY LOGIN;
