SQL Joins and Output format

Question

Can any one help me with the below sql?

**Table A**

Id  Seq   First_Name    Last_Name
1   1     John          Walter 
1   2     Michael       Jordan
1   3     Sally         May  

I want my output to look something like below where for a given Id, for each sequence number, I want to get first name and last name of the other sequence numbers.

Example Output

Id  Seq   Name
1   1     Michael Jordan | Sally May 
1   2     John Walter | Sally May
1   3     John Walter | Michael Jordan

Any help with the SQL?

Answer 1

You want to use the collect() aggregate function.

Here's a link to it's Oracle documentation.

For your case, this would be:

create or replace type names_t as table of varchar2(50);
/

create or replace function join_names(names names_t) 
                  return   varchar2
as
  ret varchar2(4000);
begin
  for i in 1 .. names.count loop

      if i > 1 then
         ret := ret || ',';
      end if;
      ret := ret || names(i);

  end loop;

  return ret;
end join_names;
/


create table tq84_table (
  id         number,
  seq        number,
  first_name varchar2(20),
  last_name  varchar2(20)
);

insert into tq84_table values (1, 1, 'John'   ,  'Walter');
insert into tq84_table values (1, 2, 'Michael',  'Jordan');
insert into tq84_table values (1, 3, 'Sally'  ,  'May'   );


select
  t1.id,
  t1.seq,
  join_names(
           cast(collect(t2.first_name || ' ' || t2.last_name order by t2.seq)
                as names_t)
  )
from
  tq84_table t1,
  tq84_table t2
where
  t1.id   =  t2.id and
  t1.seq !=  t2.seq
group by t1.id, t1.seq

If you're using Oracle 11R2 or higher, you can also use LISTAGG, which is a lot simpler (without the necessity of creating a type or function):

The query then becomes

select listagg(t2.first_name || ' ' || t2.last_name, ',') 
       within group (order by t2.seq)
         over (partition by id) as names
  from .... same as above ...