Solved

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

Posted on 2016-09-21
12
43 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 31

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 31

Expert Comment

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

Author Comment

by:tonMachine100
ID: 41808884
thanks
0
 
LVL 76

Assisted Solution

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

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
one-way data "masking" MD5 sql 26 99
Query Builder end user tool for SQL Server 1 32
T-SQL Convert to PL/SQL 23 61
null value 15 65
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now