0

Our database is on Ingres II 10.0.0 (su9.us5/132). As such, it is missing many useful functions like stuff, coalesce, etc that make merging multiple rows of data into one column supremely difficult.

There was this: How to GROUP_CONCAT in Ingres? but that solution isn't possible with how our data is structured.

Essentially there is a table that has a reference number and each row can contain that reference number multiple times if they have multiple statuses.

For example:
123 ABC
123 DEF
123 GHI
189 ABC
189 GHI

I'm pulling data from multiple tables into the one select statement, and this is just part of the data I am after. So I want the 'ABC', 'DEF', 'GHI' for example to be selected into one column, rather than ending up with three rows of the duplicate person for each status they have.

I am yet to find an option that can work.

Any ideas would be greatly appreciated.

Thanks.

Community
  • 1
  • 1
Mia
  • 23
  • 5

2 Answers2

0

I think you should be able to achieve this using a row-producing procedure.

Here's a quick example, which you can tweak to suit your needs. If you're expecting the concatenated results to be lengthy, don't forget the max length of a varchar is 32000.

create table myx(a integer not null, b char(3));
insert into myx values(123,'ABC');
insert into myx values(123,'DEF');
insert into myx values(123,'GHI');
insert into myx values(189,'ABC');
insert into myx values(189,'GHI');

create procedure myproc
result row r(a integer not null, b varchar(1000))=
declare aval=integer;
        bval=varchar(1000);
        newa=integer;
        newb=char(3);
begin
  aval=NULL;
  bval='';
  for select a,b into :newa, :newb from myx order by a,b
  do
    if :aval != :newa or :aval is null
    then
      if :aval is not null
      then
        return row(:aval, :bval);
      endif;
      aval = :newa;
      bval = :newb;
    else
      bval=:bval+','+:newb;
    endif;
  endfor;
  if :aval is not null
  then
    return row(:aval, :bval);
  endif;
end;

select * from myproc();
G Jones
  • 357
  • 1
  • 6
0

I would suggest upgrading to Actian Ingres 11.2, which have included the LISTAGG() function, as well as a lot of new features. This aggregate function is very similar to GROUP_CONCAT.

Lawrence Fernandes
  • 146
  • 5