Re: query to combine a column

On Jul 14, 2:44 am, "Tim B" <nos..._at_someisp.ca> wrote:
> I have a query that returns something like this, which is in a pl/sql
> function:
>
> id description col3
>
> ---------------------------------
> 123 dingo stuff123
>
> 357 anteater stuff357
>
> 357 aardvark stuff357
>
> 357 wombat stuff357
>
> 677 kangaroo stuff677
>
> What I want is a query that will transform the results of the above query
> like this:
>
> id description col3
>
> -------------------------------------------------------------
> 123 dingo stuff123
>
> 357 anteater$$aardvark$$wombat stuff357
>
> 677 kangaroo stuff677
>
> I want to remove the duplicate case_nums and combine their descriptions. The
> '$$' would be for use as a marker for splitting up the string inJava.
>
> Any suggestions on how to do this, if it can be done?
>
> Alternatively - This query is used to populate a cursor, which is returned
> by the function.
>
> Is there a way to make the transformation in pl/sql and still return a
> cursor from the function?

You can also create custom aggregate function

create or replace type concat_str as object (
  str_ varchar2(4000),

  static function odciaggregateinitialize(ctx in out concat_str)     return number,

  member function odciaggregateiterate
  (
    self in out concat_str
   ,value in varchar2
  ) return number,

  member function odciaggregateterminate   (
    self in concat_str
   ,returnvalue out varchar2
   ,flags in number
  ) return number,

  member function odciaggregatemerge
  (
    self in out concat_str
   ,ctx in concat_str
  ) return number
);
/
show errors

create or replace type body concat_str is   --
  static function odciaggregateinitialize(ctx in out concat_str) return number is
  begin
    ctx := concat_str(null);
    return odciconst.success;
  end;

  --
  member function odciaggregateiterate
  (
    self in out concat_str
   ,value in varchar2
  ) return number is
  begin
    if self.str_ is null
    then
      self.str_ := value;
    else
      self.str_ := self.str_ || ';' || value;     end if;
    return odciconst.success;
  end;

  --
  member function odciaggregateterminate   (
    self in concat_str
   ,returnvalue out varchar2
   ,flags in number
  ) return number is
  begin
    returnvalue := self.str_;
    return odciconst.success;
  end;

  --
  member function odciaggregatemerge
  (
    self in out concat_str
   ,ctx in concat_str
  ) return number is
  begin
    if ctx.str_ is null
    then
      null;
    else
      self.str_ := self.str_ || ';' || ctx.str_;     end if;
    return odciconst.success;
  end;

  --
end;
/
show errors

create or replace function fce_concat_str(input varchar2) return varchar2
  parallel_enable
  aggregate using concat_str;
/
show errors

column a format a10
column b format 999
column a_concatenated format a20

--
prompt concatenated - aggregate
select b
      ,fce_concat_str(a) a_concatenated
  from (select 'a' || level a
              ,mod(level, 2) b
          from dual
        connect by level <= 5)
 group by b;

--
prompt concatenated - analytic - no sort
select a
      ,b
      ,fce_concat_str(a) over(partition by b) a_concatenated
  from (select 'a' || level a
              ,mod(level, 2) b
          from dual
        connect by level <= 5);

--
prompt concatenated - analytic - sort
prompt (default = rows between unbounded preceding and current row)
select a
      ,b
      ,fce_concat_str(a) over(partition by b order by a)
a_concatenated
  from (select 'a' || level a
              ,mod(level, 2) b
          from dual
        connect by level <= 5);

--
prompt concatenated - analytic - sort
prompt (rows between unbounded preceding and unbounded following)
select a
      ,b
      ,fce_concat_str(a) over(partition by b order by a rows between
unbounded preceding and unbounded following) a_concatenated
  from (select 'a' || level a
              ,mod(level, 2) b
          from dual
        connect by level <= 5);


S.