Oracle 中的字符串聚合:多行到单列 [英] String Aggregation in Oracle: Multiple Rows into Single Column

问题描述

我在 oracle 中有以下用于字符串聚合的函数

hi I have following function for string aggregation in oracle

CREATE OR REPLACE FUNCTION STRING_AGGREGATE(i_query VARCHAR2,
i_seperator VARCHAR2 DEFAULT ',')
RETURN VARCHAR2
AS
  l_return CLOB:='';
  l_temp VARCHAR(32000);
  TYPE r_cursor is REF CURSOR;
  rc r_cursor;
BEGIN
  OPEN rc FOR i_query;
  LOOP
    FETCH rc
    INTO L_TEMP;
    EXIT WHEN RC%NOTFOUND;
    l_return:=l_return||L_TEMP||i_seperator;
  END LOOP;
  RETURN RTRIM(l_return,i_seperator);
END;

当我调用这个函数时，它显示如下

when i call this function it show like this

SELECT STRING_AGGREGATE('select ename from emp') ENAMES FROM DUAL;

ENAMES
---------------------------
SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

这个函数在这里工作得很好，但是当我用带有空格的多列调用这个函数时，它给了我错误，这是我的查询

this function working good here but when i call this function with multiple column with space it give me error here is my query

SELECT STRING_AGGREGATE('select ename||' Job is '||Job from emp') ENAMES FROM DUAL;

我想得到结果

smith job is clerk,allen job is accountatnt,ward job is programmer etc..

请指导我如何实现这一点

please guide me how to achieve this

推荐答案

你必须摆脱单引号

SELECT STRING_AGGREGATE('select ename||'' Job is ''||Job from emp') ENAMES FROM DUAL;

你可以试试像这样将字符串传递给函数

You can try out out how you pass the string to the function like this

SELECT 'select ename||'' Job is ''||Job from emp' FROM DUAL;

给你

select ename||' Job is '||Job from emp

看演示:http://sqlfiddle.com/#!2/d41d8/23283

(顺便说一下.自 Oracle 11g 以来，有一个新特性 LISTAGG，您可能还想看看.)

(By the way. There is a new feature LISTAGG since Oracle 11g which you may also want to have a look at.)

这篇关于Oracle 中的字符串聚合:多行到单列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！