?
Solved

oracle sql syntax - concatenate unique values

Posted on 2016-09-21
7
Medium Priority
?
82 Views
Last Modified: 2016-11-02
I've got a series of names in up to eight columns (Name1 - Name8).

ee3
I'd like to combine the unique values into a new column - names.

ee4

is there a way of doing this without a loads of case statements eg:

case 
when FamilySize= 1 then X1

when FamilySize= 2 then 
  (case when Name1 = Name2 then Name1 else Name1 || ' / ' || Name2  end)

when FamilySize= 3 then
  (case 
  when Name1 = Name2 and Name2 = Name3 then Name1 
  when Name1 = Name2 and Name2 <> Name3 then Name1 || ' / ' || Name3 
  when Name1 <> Name2 and Name2 <> Name3 then Name1 || ' / ' || Name2 || ' / ' || Name3 end)

when FamilySize= 4 then
  (case 
  when Name1 = Name2 and Name2 = Name3 and Name3 = Name4 then Name1 
  when Name1 = Name2 and Name2 = Name3 and Name3 <> Name4 then Name1 || ' / ' || Name2 

etc....
end names

Open in new window



thanks
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
  • 2
  • 2
7 Comments
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 41808945
0
 

Author Comment

by:tonMachine100
ID: 41810273
this is not the same question. i do want the same output, but the dataset i was working with in the previous question

fig10.PNG

is not the same as the dataset im working with in this question.

fig11.PNG
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 41810285
I don't see it that way, but anyways...
Btw: the column 'FamilySize' is 1. redundant because its value can be derived from the name columns (if filled or not) and 2. might by dangerous to use then (you may end up in a mess if this is your base for string formatting -> e.g. "if FamilySize = 3 .... " -> what happens if only 2 name columns were filled?!)
You should really avoid things like that and clean up your DB/table structure ;-)
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 41810859
Pretty much the same.  All you need to do remove the dupes across the columns.

Here is what I came up with:
/*
drop table tab1 purge;
create table tab1(familyid number, name1 varchar2(10), name2 varchar2(10), name3 varchar2(10));
insert into tab1 values(1,'a','a','a');
insert into tab1 values(2,'c','a','a');
insert into tab1 values(3,'c','b','a');
commit;
*/

SELECT familyid,
        RTRIM(
            EXTRACT(XMLAGG(XMLELEMENT("s", column_value || '/')), '/s/text()').getstringval(),
            '/'
        ) mynames
from tab1, table(set(ora_mining_varchar2_nt(name1,name2,name3))) e
group by familyid
/

Open in new window

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41869266
Sorry to object but #a41810859 produces the exact required output.

It also uses the set operator which I've not seen a lot of out there.  I've seen even less use of the ora_mining_varchar2_nt build in collection.

I would hate to lose that from the site.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

649 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