Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-21
12
Medium Priority
?
55 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 49

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 14

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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 14

Accepted Solution

by:
Alexander Eßer [Alex140181] earned 800 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 14

Assisted Solution

by:Alexander Eßer [Alex140181]
Alexander Eßer [Alex140181] earned 800 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 400 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 14

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 800 total points
ID: 41808958
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 800 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

609 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