Mixing
How can I eliminate duplicate data in multiple columns query
1
I just asked the question about how I eliminate duplicate data in a column
How can I eliminate duplicate data in column
this code below can delete duplicates in a column
with data as
(
select 'apple, apple, apple, apple' col from dual
)
select listagg(col, ',') within group(order by 1) col
from (
select distinct regexp_substr(col, '[^,]+', 1, level) col
from data
connect by level <= regexp_count(col, ',')
)
next question is
now I do not know how to eliminate data in multiple columns
select 'apple, apple, apple' as col1,
'prince,prince,princess' as col2,
'dog, cat, cat' as col3
from dual;
I would like to show
COL1 COL2 COL3
----- ---------------- --------
apple prince, princess dog, cat
sql oracle
edited Nov 19 '18 at 16:54
Barbaros Özhan
12.3k71530
asked Nov 19 '18 at 15:54
supercool djkazu
5261410
add a comment |
1
I just asked the question about how I eliminate duplicate data in a column
How can I eliminate duplicate data in column
this code below can delete duplicates in a column
with data as
(
select 'apple, apple, apple, apple' col from dual
)
select listagg(col, ',') within group(order by 1) col
from (
select distinct regexp_substr(col, '[^,]+', 1, level) col
from data
connect by level <= regexp_count(col, ',')
)
next question is
now I do not know how to eliminate data in multiple columns
select 'apple, apple, apple' as col1,
'prince,prince,princess' as col2,
'dog, cat, cat' as col3
from dual;
I would like to show
COL1 COL2 COL3
----- ---------------- --------
apple prince, princess dog, cat
sql oracle
edited Nov 19 '18 at 16:54
Barbaros Özhan
12.3k71530
asked Nov 19 '18 at 15:54
supercool djkazu
5261410
1
Why do you want to do this exercise and more importantly why in the world do you store all these delimited strings in single columns? It is a bad design practice and should be avoided at all cost. You will get a solution from someone here no doubt, but it is not worth putting such a dreadful structure in production code. The better approach should be to revamp the table structure / schema by following the rules of normalisation.
– Kaushik Nayak
Nov 19 '18 at 16:11
add a comment |
1
1
1
I just asked the question about how I eliminate duplicate data in a column
How can I eliminate duplicate data in column
this code below can delete duplicates in a column
with data as
(
select 'apple, apple, apple, apple' col from dual
)
select listagg(col, ',') within group(order by 1) col
from (
select distinct regexp_substr(col, '[^,]+', 1, level) col
from data
connect by level <= regexp_count(col, ',')
)
next question is
now I do not know how to eliminate data in multiple columns
select 'apple, apple, apple' as col1,
'prince,prince,princess' as col2,
'dog, cat, cat' as col3
from dual;
I would like to show
COL1 COL2 COL3
----- ---------------- --------
apple prince, princess dog, cat
sql oracle
edited Nov 19 '18 at 16:54
Barbaros Özhan
12.3k71530
asked Nov 19 '18 at 15:54
supercool djkazu
5261410
I just asked the question about how I eliminate duplicate data in a column
How can I eliminate duplicate data in column
this code below can delete duplicates in a column
with data as
(
select 'apple, apple, apple, apple' col from dual
)
select listagg(col, ',') within group(order by 1) col
from (
select distinct regexp_substr(col, '[^,]+', 1, level) col
from data
connect by level <= regexp_count(col, ',')
)
next question is
now I do not know how to eliminate data in multiple columns
select 'apple, apple, apple' as col1,
'prince,prince,princess' as col2,
'dog, cat, cat' as col3
from dual;
I would like to show
COL1 COL2 COL3
----- ---------------- --------
apple prince, princess dog, cat
sql oracle
sql oracle
edited Nov 19 '18 at 16:54
Barbaros Özhan
12.3k71530
asked Nov 19 '18 at 15:54
supercool djkazu
5261410
edited Nov 19 '18 at 16:54
Barbaros Özhan
12.3k71530
asked Nov 19 '18 at 15:54
supercool djkazu
5261410
edited Nov 19 '18 at 16:54
Barbaros Özhan
12.3k71530
edited Nov 19 '18 at 16:54
Barbaros Özhan
12.3k71530
edited Nov 19 '18 at 16:54
Barbaros Özhan
12.3k71530
12.3k71530
asked Nov 19 '18 at 15:54
supercool djkazu
5261410
asked Nov 19 '18 at 15:54
supercool djkazu
5261410
asked Nov 19 '18 at 15:54
supercool djkazu
5261410
5261410
1
Why do you want to do this exercise and more importantly why in the world do you store all these delimited strings in single columns? It is a bad design practice and should be avoided at all cost. You will get a solution from someone here no doubt, but it is not worth putting such a dreadful structure in production code. The better approach should be to revamp the table structure / schema by following the rules of normalisation.
– Kaushik Nayak
Nov 19 '18 at 16:11
add a comment |
1
Why do you want to do this exercise and more importantly why in the world do you store all these delimited strings in single columns? It is a bad design practice and should be avoided at all cost. You will get a solution from someone here no doubt, but it is not worth putting such a dreadful structure in production code. The better approach should be to revamp the table structure / schema by following the rules of normalisation.
– Kaushik Nayak
Nov 19 '18 at 16:11
1
1
Why do you want to do this exercise and more importantly why in the world do you store all these delimited strings in single columns? It is a bad design practice and should be avoided at all cost. You will get a solution from someone here no doubt, but it is not worth putting such a dreadful structure in production code. The better approach should be to revamp the table structure / schema by following the rules of normalisation.
– Kaushik Nayak
Nov 19 '18 at 16:11
Why do you want to do this exercise and more importantly why in the world do you store all these delimited strings in single columns? It is a bad design practice and should be avoided at all cost. You will get a solution from someone here no doubt, but it is not worth putting such a dreadful structure in production code. The better approach should be to revamp the table structure / schema by following the rules of normalisation.
– Kaushik Nayak
Nov 19 '18 at 16:11
add a comment |
1 Answer
1
active
oldest
votes
1
You may use such a combination :
select
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('apple, apple, apple','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('apple, apple, apple',',') + 1
)
) as str1,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('prince,prince,princess','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('prince,prince,princess',',') + 1
)
) as str2,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('dog, cat, cat','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('dog, cat, cat',',') + 1
)
) as str3
from dual;
STR1 STR2 STR3
------ --------------- --------
apple prince,princess cat,dog
Rextester Demo
answered Nov 19 '18 at 16:25
Barbaros Özhan
12.3k71530
Thanks you so much!!!
– supercool djkazu
Nov 19 '18 at 16:38
@supercooldjkazu you're very welcome friend.
– Barbaros Özhan
Nov 19 '18 at 16:51
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53378337%2fhow-can-i-eliminate-duplicate-data-in-multiple-columns-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You may use such a combination :
select
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('apple, apple, apple','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('apple, apple, apple',',') + 1
)
) as str1,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('prince,prince,princess','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('prince,prince,princess',',') + 1
)
) as str2,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('dog, cat, cat','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('dog, cat, cat',',') + 1
)
) as str3
from dual;
STR1 STR2 STR3
------ --------------- --------
apple prince,princess cat,dog
Rextester Demo
answered Nov 19 '18 at 16:25
Barbaros Özhan
12.3k71530
Thanks you so much!!!
– supercool djkazu
Nov 19 '18 at 16:38
@supercooldjkazu you're very welcome friend.
– Barbaros Özhan
Nov 19 '18 at 16:51
add a comment |
1
You may use such a combination :
select
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('apple, apple, apple','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('apple, apple, apple',',') + 1
)
) as str1,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('prince,prince,princess','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('prince,prince,princess',',') + 1
)
) as str2,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('dog, cat, cat','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('dog, cat, cat',',') + 1
)
) as str3
from dual;
STR1 STR2 STR3
------ --------------- --------
apple prince,princess cat,dog
Rextester Demo
answered Nov 19 '18 at 16:25
Barbaros Özhan
12.3k71530
Thanks you so much!!!
– supercool djkazu
Nov 19 '18 at 16:38
@supercooldjkazu you're very welcome friend.
– Barbaros Özhan
Nov 19 '18 at 16:51
add a comment |
1
1
1
You may use such a combination :
select
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('apple, apple, apple','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('apple, apple, apple',',') + 1
)
) as str1,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('prince,prince,princess','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('prince,prince,princess',',') + 1
)
) as str2,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('dog, cat, cat','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('dog, cat, cat',',') + 1
)
) as str3
from dual;
STR1 STR2 STR3
------ --------------- --------
apple prince,princess cat,dog
Rextester Demo
answered Nov 19 '18 at 16:25
Barbaros Özhan
12.3k71530
You may use such a combination :
select
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('apple, apple, apple','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('apple, apple, apple',',') + 1
)
) as str1,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('prince,prince,princess','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('prince,prince,princess',',') + 1
)
) as str2,
(
select listagg(str,',') within group (order by 0)
from
(
select distinct trim(regexp_substr('dog, cat, cat','[^,]+', 1, level)) as str
from dual
connect by level <= regexp_count('dog, cat, cat',',') + 1
)
) as str3
from dual;
STR1 STR2 STR3
------ --------------- --------
apple prince,princess cat,dog
Rextester Demo
answered Nov 19 '18 at 16:25
Barbaros Özhan
12.3k71530
answered Nov 19 '18 at 16:25
Barbaros Özhan
12.3k71530
answered Nov 19 '18 at 16:25
Barbaros Özhan
12.3k71530
answered Nov 19 '18 at 16:25
Barbaros Özhan
12.3k71530
12.3k71530
Thanks you so much!!!
– supercool djkazu
Nov 19 '18 at 16:38
@supercooldjkazu you're very welcome friend.
– Barbaros Özhan
Nov 19 '18 at 16:51
add a comment |
Thanks you so much!!!
– supercool djkazu
Nov 19 '18 at 16:38
@supercooldjkazu you're very welcome friend.
– Barbaros Özhan
Nov 19 '18 at 16:51
Thanks you so much!!!
– supercool djkazu
Nov 19 '18 at 16:38
Thanks you so much!!!
– supercool djkazu
Nov 19 '18 at 16:38
@supercooldjkazu you're very welcome friend.
– Barbaros Özhan
Nov 19 '18 at 16:51
@supercooldjkazu you're very welcome friend.
– Barbaros Özhan
Nov 19 '18 at 16:51
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53378337%2fhow-can-i-eliminate-duplicate-data-in-multiple-columns-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Why do you want to do this exercise and more importantly why in the world do you store all these delimited strings in single columns? It is a bad design practice and should be avoided at all cost. You will get a solution from someone here no doubt, but it is not worth putting such a dreadful structure in production code. The better approach should be to revamp the table structure / schema by following the rules of normalisation.
– Kaushik Nayak
Nov 19 '18 at 16:11