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

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
tonMachine100Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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?
0
Alex [***Alex140181***]Software DeveloperCommented:
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
0
tonMachine100Author 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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Alex [***Alex140181***]Software DeveloperCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alex [***Alex140181***]Software DeveloperCommented:
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

0
awking00Information Technology SpecialistCommented:
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
0
Alex [***Alex140181***]Software DeveloperCommented:
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 ;-)
0
awking00Information Technology SpecialistCommented:
That was my assumption as well :-)
0
tonMachine100Author Commented:
thanks
0
slightwv (䄆 Netminder) 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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.