Create one column, from multiple columns in different tables
I SELECT multiple columns from different tables. They all have a column DocId that are merged by, after all i get the columns something like this.
Col1 Col2 Col3
-------- -------------- ----------------
10 ISNULL ISNULL
ISNULL 81 ISNULL
ISNULL ISNULL 213
51 ISNULL 13
ISNULL 2 ISNULL
84 ISNULL ISNULL
Now, what i want to get is these columns above in one, and i dont want to sum the values, because it is supposed just one of them to have value in one row... I hope i explained good.
I use complex query with outer join to get these columns.
sql
asked Nov 23 '18 at 11:32
ElisaElisa
217
add a comment |
I SELECT multiple columns from different tables. They all have a column DocId that are merged by, after all i get the columns something like this.
Col1 Col2 Col3
-------- -------------- ----------------
10 ISNULL ISNULL
ISNULL 81 ISNULL
ISNULL ISNULL 213
51 ISNULL 13
ISNULL 2 ISNULL
84 ISNULL ISNULL
Now, what i want to get is these columns above in one, and i dont want to sum the values, because it is supposed just one of them to have value in one row... I hope i explained good.
I use complex query with outer join to get these columns.
sql
asked Nov 23 '18 at 11:32
ElisaElisa
217
1
please elaborate or give sample data
– nikhil sugandh
Nov 23 '18 at 11:37
2
Hi there, it would help if the sample data is minimal and complete. What do the four tables look like individually? What is your expected output?
– TrebuchetMS
Nov 23 '18 at 11:43
@Elisa see above comment and do accordingly
– nikhil sugandh
Nov 23 '18 at 11:45
1
Sample data is great, but we need the expected result too.
– jarlh
Nov 23 '18 at 11:59
1
Please show the results you want. And what are the three tables?
– Gordon Linoff
Nov 23 '18 at 11:59
add a comment |
I SELECT multiple columns from different tables. They all have a column DocId that are merged by, after all i get the columns something like this.
Col1 Col2 Col3
-------- -------------- ----------------
10 ISNULL ISNULL
ISNULL 81 ISNULL
ISNULL ISNULL 213
51 ISNULL 13
ISNULL 2 ISNULL
84 ISNULL ISNULL
Now, what i want to get is these columns above in one, and i dont want to sum the values, because it is supposed just one of them to have value in one row... I hope i explained good.
I use complex query with outer join to get these columns.
sql
asked Nov 23 '18 at 11:32
ElisaElisa
217
I SELECT multiple columns from different tables. They all have a column DocId that are merged by, after all i get the columns something like this.
Col1 Col2 Col3
-------- -------------- ----------------
10 ISNULL ISNULL
ISNULL 81 ISNULL
ISNULL ISNULL 213
51 ISNULL 13
ISNULL 2 ISNULL
84 ISNULL ISNULL
Now, what i want to get is these columns above in one, and i dont want to sum the values, because it is supposed just one of them to have value in one row... I hope i explained good.
I use complex query with outer join to get these columns.
sql
sql
asked Nov 23 '18 at 11:32
ElisaElisa
217
asked Nov 23 '18 at 11:32
ElisaElisa
217
edited Nov 23 '18 at 12:06
Elisa
asked Nov 23 '18 at 11:32
ElisaElisa
217
asked Nov 23 '18 at 11:32
ElisaElisa
217
asked Nov 23 '18 at 11:32
ElisaElisa
217
217
1
please elaborate or give sample data
– nikhil sugandh
Nov 23 '18 at 11:37
2
Hi there, it would help if the sample data is minimal and complete. What do the four tables look like individually? What is your expected output?
– TrebuchetMS
Nov 23 '18 at 11:43
@Elisa see above comment and do accordingly
– nikhil sugandh
Nov 23 '18 at 11:45
1
Sample data is great, but we need the expected result too.
– jarlh
Nov 23 '18 at 11:59
1
Please show the results you want. And what are the three tables?
– Gordon Linoff
Nov 23 '18 at 11:59
add a comment |
1
please elaborate or give sample data
– nikhil sugandh
Nov 23 '18 at 11:37
2
Hi there, it would help if the sample data is minimal and complete. What do the four tables look like individually? What is your expected output?
– TrebuchetMS
Nov 23 '18 at 11:43
@Elisa see above comment and do accordingly
– nikhil sugandh
Nov 23 '18 at 11:45
1
Sample data is great, but we need the expected result too.
– jarlh
Nov 23 '18 at 11:59
1
Please show the results you want. And what are the three tables?
– Gordon Linoff
Nov 23 '18 at 11:59
1
1
please elaborate or give sample data
– nikhil sugandh
Nov 23 '18 at 11:37
please elaborate or give sample data
– nikhil sugandh
Nov 23 '18 at 11:37
2
2
Hi there, it would help if the sample data is minimal and complete. What do the four tables look like individually? What is your expected output?
– TrebuchetMS
Nov 23 '18 at 11:43
Hi there, it would help if the sample data is minimal and complete. What do the four tables look like individually? What is your expected output?
– TrebuchetMS
Nov 23 '18 at 11:43
@Elisa see above comment and do accordingly
– nikhil sugandh
Nov 23 '18 at 11:45
@Elisa see above comment and do accordingly
– nikhil sugandh
Nov 23 '18 at 11:45
1
1
Sample data is great, but we need the expected result too.
– jarlh
Nov 23 '18 at 11:59
Sample data is great, but we need the expected result too.
– jarlh
Nov 23 '18 at 11:59
1
1
Please show the results you want. And what are the three tables?
– Gordon Linoff
Nov 23 '18 at 11:59
Please show the results you want. And what are the three tables?
– Gordon Linoff
Nov 23 '18 at 11:59
add a comment |
3 Answers
3
active
oldest
votes
You can use the set operators. If you want to remove duplicates use UNION if you want to retrieve all data including duplicates use UNION ALL. Below is an example for a two column combination, just extend it to four.
SELECT col1 FROM <table_name>
WHERE col1 IS NOT NULL
UNION
SELECT col2 FROM <table_name>
WHERE col2 IS NOT NULL;
This should give you a single column with no NULL values.You can combine as many set operators as you like. Just repeat this for columns 3 and 4.
answered Nov 23 '18 at 11:48
m_e_sm_e_s
11
add a comment |
not much information but if you want to create a view starting from 3 tables and with one column from each table you can:
create view v
as
select colum1, null as Column2,null as column3 from table1
union
select null as colum1, Column2,null as column3 from table2
union
select null as colum1, null as Column2, column3 from table3
then if you don't need null values you can filter the view where column x is not null or each table used in union with columnx is not null
answered Nov 23 '18 at 11:49
picklerickpicklerick
32918
add a comment |
this will work:
create or replace view view_name as
select nvl(a.col1,' ')||nvl(a.col2,' ')||vl(a.col3,' ')||nvl(a.col4,' ') column1
from table_name a;
or this:
create or replace view view_name as
select decode(a.A||a.B||a.C||a.D, 'NULL','ISNULL',
(nvl(a.A,'')||nvl(a.B,'')||nvl(a.C,'')||nvl(a.D,''))) column1
from table_name a;
check:http://sqlfiddle.com/#!4/09aa6/3/0
answered Nov 23 '18 at 11:48
nikhil sugandhnikhil sugandh
1,2762719
for numbers just replace || with + and nvl to nvl(field name,0) you wll get what you want
– nikhil sugandh
Nov 23 '18 at 12:19
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
});
}
});
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%2f53445912%2fcreate-one-column-from-multiple-columns-in-different-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can use the set operators. If you want to remove duplicates use UNION if you want to retrieve all data including duplicates use UNION ALL. Below is an example for a two column combination, just extend it to four.
SELECT col1 FROM <table_name>
WHERE col1 IS NOT NULL
UNION
SELECT col2 FROM <table_name>
WHERE col2 IS NOT NULL;
This should give you a single column with no NULL values.You can combine as many set operators as you like. Just repeat this for columns 3 and 4.
answered Nov 23 '18 at 11:48
m_e_sm_e_s
11
add a comment |
You can use the set operators. If you want to remove duplicates use UNION if you want to retrieve all data including duplicates use UNION ALL. Below is an example for a two column combination, just extend it to four.
SELECT col1 FROM <table_name>
WHERE col1 IS NOT NULL
UNION
SELECT col2 FROM <table_name>
WHERE col2 IS NOT NULL;
This should give you a single column with no NULL values.You can combine as many set operators as you like. Just repeat this for columns 3 and 4.
answered Nov 23 '18 at 11:48
m_e_sm_e_s
11
add a comment |
You can use the set operators. If you want to remove duplicates use UNION if you want to retrieve all data including duplicates use UNION ALL. Below is an example for a two column combination, just extend it to four.
SELECT col1 FROM <table_name>
WHERE col1 IS NOT NULL
UNION
SELECT col2 FROM <table_name>
WHERE col2 IS NOT NULL;
This should give you a single column with no NULL values.You can combine as many set operators as you like. Just repeat this for columns 3 and 4.
answered Nov 23 '18 at 11:48
m_e_sm_e_s
11
You can use the set operators. If you want to remove duplicates use UNION if you want to retrieve all data including duplicates use UNION ALL. Below is an example for a two column combination, just extend it to four.
SELECT col1 FROM <table_name>
WHERE col1 IS NOT NULL
UNION
SELECT col2 FROM <table_name>
WHERE col2 IS NOT NULL;
This should give you a single column with no NULL values.You can combine as many set operators as you like. Just repeat this for columns 3 and 4.
answered Nov 23 '18 at 11:48
m_e_sm_e_s
11
answered Nov 23 '18 at 11:48
m_e_sm_e_s
11
answered Nov 23 '18 at 11:48
m_e_sm_e_s
11
answered Nov 23 '18 at 11:48
m_e_sm_e_s
11
11
add a comment |
add a comment |
not much information but if you want to create a view starting from 3 tables and with one column from each table you can:
create view v
as
select colum1, null as Column2,null as column3 from table1
union
select null as colum1, Column2,null as column3 from table2
union
select null as colum1, null as Column2, column3 from table3
then if you don't need null values you can filter the view where column x is not null or each table used in union with columnx is not null
answered Nov 23 '18 at 11:49
picklerickpicklerick
32918
add a comment |
not much information but if you want to create a view starting from 3 tables and with one column from each table you can:
create view v
as
select colum1, null as Column2,null as column3 from table1
union
select null as colum1, Column2,null as column3 from table2
union
select null as colum1, null as Column2, column3 from table3
then if you don't need null values you can filter the view where column x is not null or each table used in union with columnx is not null
answered Nov 23 '18 at 11:49
picklerickpicklerick
32918
add a comment |
not much information but if you want to create a view starting from 3 tables and with one column from each table you can:
create view v
as
select colum1, null as Column2,null as column3 from table1
union
select null as colum1, Column2,null as column3 from table2
union
select null as colum1, null as Column2, column3 from table3
then if you don't need null values you can filter the view where column x is not null or each table used in union with columnx is not null
answered Nov 23 '18 at 11:49
picklerickpicklerick
32918
not much information but if you want to create a view starting from 3 tables and with one column from each table you can:
create view v
as
select colum1, null as Column2,null as column3 from table1
union
select null as colum1, Column2,null as column3 from table2
union
select null as colum1, null as Column2, column3 from table3
then if you don't need null values you can filter the view where column x is not null or each table used in union with columnx is not null
answered Nov 23 '18 at 11:49
picklerickpicklerick
32918
answered Nov 23 '18 at 11:49
picklerickpicklerick
32918
answered Nov 23 '18 at 11:49
picklerickpicklerick
32918
answered Nov 23 '18 at 11:49
picklerickpicklerick
32918
32918
add a comment |
add a comment |
this will work:
create or replace view view_name as
select nvl(a.col1,' ')||nvl(a.col2,' ')||vl(a.col3,' ')||nvl(a.col4,' ') column1
from table_name a;
or this:
create or replace view view_name as
select decode(a.A||a.B||a.C||a.D, 'NULL','ISNULL',
(nvl(a.A,'')||nvl(a.B,'')||nvl(a.C,'')||nvl(a.D,''))) column1
from table_name a;
check:http://sqlfiddle.com/#!4/09aa6/3/0
answered Nov 23 '18 at 11:48
nikhil sugandhnikhil sugandh
1,2762719
for numbers just replace || with + and nvl to nvl(field name,0) you wll get what you want
– nikhil sugandh
Nov 23 '18 at 12:19
add a comment |
this will work:
create or replace view view_name as
select nvl(a.col1,' ')||nvl(a.col2,' ')||vl(a.col3,' ')||nvl(a.col4,' ') column1
from table_name a;
or this:
create or replace view view_name as
select decode(a.A||a.B||a.C||a.D, 'NULL','ISNULL',
(nvl(a.A,'')||nvl(a.B,'')||nvl(a.C,'')||nvl(a.D,''))) column1
from table_name a;
check:http://sqlfiddle.com/#!4/09aa6/3/0
answered Nov 23 '18 at 11:48
nikhil sugandhnikhil sugandh
1,2762719
for numbers just replace || with + and nvl to nvl(field name,0) you wll get what you want
– nikhil sugandh
Nov 23 '18 at 12:19
add a comment |
this will work:
create or replace view view_name as
select nvl(a.col1,' ')||nvl(a.col2,' ')||vl(a.col3,' ')||nvl(a.col4,' ') column1
from table_name a;
or this:
create or replace view view_name as
select decode(a.A||a.B||a.C||a.D, 'NULL','ISNULL',
(nvl(a.A,'')||nvl(a.B,'')||nvl(a.C,'')||nvl(a.D,''))) column1
from table_name a;
check:http://sqlfiddle.com/#!4/09aa6/3/0
answered Nov 23 '18 at 11:48
nikhil sugandhnikhil sugandh
1,2762719
this will work:
create or replace view view_name as
select nvl(a.col1,' ')||nvl(a.col2,' ')||vl(a.col3,' ')||nvl(a.col4,' ') column1
from table_name a;
or this:
create or replace view view_name as
select decode(a.A||a.B||a.C||a.D, 'NULL','ISNULL',
(nvl(a.A,'')||nvl(a.B,'')||nvl(a.C,'')||nvl(a.D,''))) column1
from table_name a;
check:http://sqlfiddle.com/#!4/09aa6/3/0
answered Nov 23 '18 at 11:48
nikhil sugandhnikhil sugandh
1,2762719
edited Nov 23 '18 at 12:16
answered Nov 23 '18 at 11:48
nikhil sugandhnikhil sugandh
1,2762719
answered Nov 23 '18 at 11:48
nikhil sugandhnikhil sugandh
1,2762719
answered Nov 23 '18 at 11:48
nikhil sugandhnikhil sugandh
1,2762719
1,2762719
for numbers just replace || with + and nvl to nvl(field name,0) you wll get what you want
– nikhil sugandh
Nov 23 '18 at 12:19
add a comment |
for numbers just replace || with + and nvl to nvl(field name,0) you wll get what you want
– nikhil sugandh
Nov 23 '18 at 12:19
for numbers just replace || with + and nvl to nvl(field name,0) you wll get what you want
– nikhil sugandh
Nov 23 '18 at 12:19
for numbers just replace || with + and nvl to nvl(field name,0) you wll get what you want
– nikhil sugandh
Nov 23 '18 at 12:19
add a comment |
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.
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%2f53445912%2fcreate-one-column-from-multiple-columns-in-different-tables%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
please elaborate or give sample data
– nikhil sugandh
Nov 23 '18 at 11:37
2
Hi there, it would help if the sample data is minimal and complete. What do the four tables look like individually? What is your expected output?
– TrebuchetMS
Nov 23 '18 at 11:43
@Elisa see above comment and do accordingly
– nikhil sugandh
Nov 23 '18 at 11:45
1
Sample data is great, but we need the expected result too.
– jarlh
Nov 23 '18 at 11:59
1
Please show the results you want. And what are the three tables?
– Gordon Linoff
Nov 23 '18 at 11:59