concatenating values from multiple rows onto one column without grouping - oracle sql

tonMachine100
tonMachine100 used Ask the Experts™
on
i have a dataset which records family members. see attached fig1.

fig1
I need some syntax that will out put the results as mocked up in fig2.

fig2
the last column family_surnames pulls out the distinct client_surnames in the family and concatenates them.

We're using oracle 10 so cant use list_agg. List_agg looks like it would have done the job (as shown here http://www.exforsys.com/tutorials/oracle-11g/oracle-analytic-enhancements.html)  but i believe that is only supported in 11.

any help is appreciated
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
list_AGG: group by provides the ability to do AGGregations

why are you excluding group by? if you get the desired result does it matter if we propose group by?
You may use the WM_CONCAT built-in function ;-)
BUT: be aware!!
WM_CONCAT is an undocumented function and as such is not supported by Oracle for user applications (MOS Note ID 1336219.1). If this concerns you, use a User-Defined Aggregate Function described below.

Also, WM_CONCAT has been removed from 12c onward, so you can't pick this option.

Taken from: https://oracle-base.com/articles/misc/string-aggregation-techniques

Author

Commented:
PortletPaul - no you can propose group by if it gives me the desired result. so long as it'll work in oracle 10.

Alexander Eßer [Alex140181] - Thanks for the link. Using wm_concat as suggested in the examples would group by family id so show only one row per family id (7 rows). i need the query to retrun all (15) rows
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Just off the top of my head ;-)

My setup:
create table ee_test
  (
   team_person_id number,
   team_code_id number,
   fam_id number,
   client_surname varchar2(100)
  );

insert all 
  into ee_test(team_person_id, team_code_id, fam_id, client_surname) values (244817, 57097, 1, 'Yasin')
  into ee_test(team_person_id, team_code_id, fam_id, client_surname) values (244818, 57097, 1, 'Yasin')
  into ee_test(team_person_id, team_code_id, fam_id, client_surname) values (244819, 57097, 1, 'Yasin')
  into ee_test(team_person_id, team_code_id, fam_id, client_surname) values (437728, 57097, 2, 'Griffiths')
  into ee_test(team_person_id, team_code_id, fam_id, client_surname) values (437731, 57097, 2, 'Fairest-Callaghan')
  into ee_test(team_person_id, team_code_id, fam_id, client_surname) values (1353108, 57097, 2, 'Fairest')
  into ee_test(team_person_id, team_code_id, fam_id, client_surname) values (566327, 57097, 3, 'Middlebrook')
  into ee_test(team_person_id, team_code_id, fam_id, client_surname) values (566328, 57097, 3, 'Middlebrook')
select * from dual;

Open in new window


Query:
select a.team_person_id,
       a.team_code_id,
       a.fam_id,
       (select wm_concat(distinct x.client_surname)
          from ee_test x
         where x.team_code_id = a.team_code_id
           and x.fam_id = a.fam_id
         group by x.team_code_id,
                  x.fam_id)
  from ee_test a;

Open in new window

Whereas this one should be working, too:
select a.team_person_id,
       a.team_code_id,
       a.fam_id,
       y.client_surname_x
  from ee_test a
  join (select x.team_code_id,
               x.fam_id,
               wm_concat(distinct x.client_surname) client_surname_x
          from ee_test x
         group by x.team_code_id,
                  x.fam_id) y
    on (y.team_code_id = a.team_code_id and y.fam_id = a.fam_id);

Open in new window

awking00Information Technology Specialist
Commented:
You can use row_number() and sys_connect_by_path that was available in 9i -

select a.team_person_id, a.team_code_id, a.fam_id, a.client_surname, b.family_surnames from
(select team_person_id, team_code_id, fam_id, client_surname
 from yourtable) a
left join
(select fam_id,
 ltrim(max(sys_connect_by_path(client_surname,'/')) keep (dense_rank last order by rn),'/') as family_surnames from
 (select fam_id, client_surname,
  row_number() over (partition by fam_id order by client_surname) as rn,
  row_number() over (partition by fam_id order by client_surname) - 1 as prev
  from yourtable)
 group by fam_id
 connect by prev = prior rn and fam_id = prior fam_id and client_surname != prior client_surname
 start with rn = 1) b
on a.fam_id = b.fam_id;

Open in new window


Note - this will produce Bellamy/Smith for fam_id = 6 although that's not what your mocked up results show
Note - this will produce Bellamy/Smith for fam_id = 6 although that's not what your mocked up results show
I suppose he just simply missed that one when editing the result grid for the mock ups ;-)
awking00Information Technology Specialist

Commented:
That was my assumption as well :-)

Author

Commented:
thanks
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Here is what I came up with (pretty much the same idea as your other question:
/*
drop table tab1 purge;
create table tab1(familyid number, name1 varchar2(10));
insert into tab1 values(1,'a');
insert into tab1 values(1,'a');
insert into tab1 values(1,'a');
insert into tab1 values(2,'a');
insert into tab1 values(2,'a');
insert into tab1 values(2,'c');
insert into tab1 values(3,'a');
insert into tab1 values(3,'b');
insert into tab1 values(3,'c');
commit;
*/

select familyid,
        RTRIM(
            EXTRACT(XMLAGG(XMLELEMENT("s", column_value || '/')), '/s/text()').getstringval(),
            '/'
        ) mynames
from (
	SELECT familyid, collect(distinct name1) name_collection
	from tab1
	group by familyid
), table(name_collection)
group by familyid
/

Open in new window


I'll predict your next question now:  The only way to guarantee the order of data is with an order by.  If you don't want alphabetical order, you'll need some column in your table to determine the order.  I didn't see it in your sample data.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial