Solved

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

Posted on 2016-09-21
12
51 Views
Last Modified: 2016-11-06
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
0
Comment
Question by:tonMachine100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +2
12 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41808493
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 41808499
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
 

Author Comment

by:tonMachine100
ID: 41808529
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 13

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 200 total points
ID: 41808564
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
 
LVL 13

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 200 total points
ID: 41808570
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 41808802
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
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 41808816
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
 
LVL 32

Expert Comment

by:awking00
ID: 41808831
That was my assumption as well :-)
0
 

Author Comment

by:tonMachine100
ID: 41808884
thanks
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 41808958
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 41810858
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

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Developer - SubString 2 51
SP result not being displayed 5 68
UPDATE JOIN multiple tables 5 34
When to use a Unique Index? A Clustered Index? 5 76
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question