oracle sql syntax - concatenate unique values

I've got a series of names in up to eight columns (Name1 - Name8).

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


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

when FamilySize= 1 then X1

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

when FamilySize= 3 then
  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
  when Name1 = Name2 and Name2 = Name3 and Name3 = Name4 then Name1 
  when Name1 = Name2 and Name2 = Name3 and Name3 <> Name4 then Name1 || ' / ' || Name2 

end names

Open in new window

Who is Participating?

Improve company productivity with a Business Account.Sign Up

slightwv (䄆 Netminder)Connect With a Mentor Commented:
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');

SELECT familyid,
            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

Alexander Eßer [Alex140181]Software DeveloperCommented:
tonMachine100Author Commented:
this is not the same question. i do want the same output, but the dataset i was working with in the previous question


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

Alexander Eßer [Alex140181]Software DeveloperCommented:
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 ;-)
slightwv (䄆 Netminder) Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.