I need to create a oracle table1 from another table2 in oracle. where i need to insert the data as the column...
0
Example:
Table2 (original table)
custid 01-jan-2018 07-jan-2018 14-jan-2018 21-jan-2018
102 7 2 5 4
Need to create a table like below (Table1):
custid date data
102 01-jan-2018 7
102 07-jan-2018 2
102 14-jan-2018 5
102 21-jan-2018 4
Please advise how can I achieve/create table1 from table2.
sql oracle unpivot
edited Nov 14 '18 at 16:15
Barbaros Özhan
13.4k71633
asked Nov 14 '18 at 15:09
VirenderVirender
34
add a comment |
0
Example:
Table2 (original table)
custid 01-jan-2018 07-jan-2018 14-jan-2018 21-jan-2018
102 7 2 5 4
Need to create a table like below (Table1):
custid date data
102 01-jan-2018 7
102 07-jan-2018 2
102 14-jan-2018 5
102 21-jan-2018 4
Please advise how can I achieve/create table1 from table2.
sql oracle unpivot
edited Nov 14 '18 at 16:15
Barbaros Özhan
13.4k71633
asked Nov 14 '18 at 15:09
VirenderVirender
34
Is the column creation in original table having some logic ? Eg Weekly ? Is it hard-coded to be like in your example or the solution has to be dynamic ? Eg. To work for feb,march etc. as well...
– pOrinG
Nov 14 '18 at 15:55
add a comment |
0
0
0
Example:
Table2 (original table)
custid 01-jan-2018 07-jan-2018 14-jan-2018 21-jan-2018
102 7 2 5 4
Need to create a table like below (Table1):
custid date data
102 01-jan-2018 7
102 07-jan-2018 2
102 14-jan-2018 5
102 21-jan-2018 4
Please advise how can I achieve/create table1 from table2.
sql oracle unpivot
edited Nov 14 '18 at 16:15
Barbaros Özhan
13.4k71633
asked Nov 14 '18 at 15:09
VirenderVirender
34
Example:
Table2 (original table)
custid 01-jan-2018 07-jan-2018 14-jan-2018 21-jan-2018
102 7 2 5 4
Need to create a table like below (Table1):
custid date data
102 01-jan-2018 7
102 07-jan-2018 2
102 14-jan-2018 5
102 21-jan-2018 4
Please advise how can I achieve/create table1 from table2.
sql oracle unpivot
sql oracle unpivot
edited Nov 14 '18 at 16:15
Barbaros Özhan
13.4k71633
asked Nov 14 '18 at 15:09
VirenderVirender
34
edited Nov 14 '18 at 16:15
Barbaros Özhan
13.4k71633
asked Nov 14 '18 at 15:09
VirenderVirender
34
edited Nov 14 '18 at 16:15
Barbaros Özhan
13.4k71633
edited Nov 14 '18 at 16:15
Barbaros Özhan
13.4k71633
edited Nov 14 '18 at 16:15
Barbaros Özhan
13.4k71633
13.4k71633
asked Nov 14 '18 at 15:09
VirenderVirender
34
asked Nov 14 '18 at 15:09
VirenderVirender
34
asked Nov 14 '18 at 15:09
VirenderVirender
34
34
Is the column creation in original table having some logic ? Eg Weekly ? Is it hard-coded to be like in your example or the solution has to be dynamic ? Eg. To work for feb,march etc. as well...
– pOrinG
Nov 14 '18 at 15:55
add a comment |
Is the column creation in original table having some logic ? Eg Weekly ? Is it hard-coded to be like in your example or the solution has to be dynamic ? Eg. To work for feb,march etc. as well...
– pOrinG
Nov 14 '18 at 15:55
Is the column creation in original table having some logic ? Eg Weekly ? Is it hard-coded to be like in your example or the solution has to be dynamic ? Eg. To work for feb,march etc. as well...
– pOrinG
Nov 14 '18 at 15:55
Is the column creation in original table having some logic ? Eg Weekly ? Is it hard-coded to be like in your example or the solution has to be dynamic ? Eg. To work for feb,march etc. as well...
– pOrinG
Nov 14 '18 at 15:55
add a comment |
2 Answers
2
active
oldest
votes
0
Below are steps to achieve this scenario:
create table Table2
(custid number,
"01-jan-2018" number,
"07-jan-2018" number,
"14-jan-2018" number,
"21-jan-2018" number);
insert into Table2 values (102,7,2,5,4);
create table Table3
(custid number,
"date" date,
"data" number);
insert into Table3(custid,"date","data")
(SELECT *
FROM Table2
UNPIVOT
INCLUDE NULLS
(DATA FOR COL IN
(
"01-jan-2018" ,"07-jan-2018","14-jan-2018","21-jan-2018"
)
));
select * from table3;
102 01-JAN-18 7
102 07-JAN-18 2
102 14-JAN-18 5
102 21-JAN-18 4
answered Nov 14 '18 at 16:01
kanagarajkanagaraj
35417
while inserting the data in table3 (May be due to the date field is date and we are inserting the number data. error came--- ORA-01858: a non-numeric character was found where a numeric was expected
– Virender
Nov 14 '18 at 17:37
may be you have number datatype ... I kept as date datatype for Table3 ... I did in Oracle 12c and I have no issues
– kanagaraj
Nov 14 '18 at 18:03
add a comment |
0
You may use unpivot keyword as :
create table Table1 as
with Table2(custid,d01_Jan_2018,d07_Jan_2018,d14_Jan_2018,d21_Jan_2018) as
(
select 102, 7, 2, 5, 4 from dual
)
select custid, col1 as "DATE", data from Table2
unpivot
(data for col1 in (d01_Jan_2018, d07_Jan_2018, d14_Jan_2018,d21_Jan_2018));
select * from Table1;
CUSTID DATE DATA
------ ------------ -----
102 D01_JAN_2018 7
102 D07_JAN_2018 2
102 D14_JAN_2018 5
102 D21_JAN_2018 4
SQL Fiddle Demo
answered Nov 14 '18 at 16:03
Barbaros ÖzhanBarbaros Özhan
13.4k71633
In table1 date column is real date column. I tried with same kid of your example.
– Virender
Nov 14 '18 at 17:12
@Virender what'scustidthen, a string type column wrapped in quotes as 'custid' ..?, I supposed that as a column name ...
– Barbaros Özhan
Nov 14 '18 at 17:16
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%2f53303271%2fi-need-to-create-a-oracle-table1-from-another-table2-in-oracle-where-i-need-to%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Below are steps to achieve this scenario:
create table Table2
(custid number,
"01-jan-2018" number,
"07-jan-2018" number,
"14-jan-2018" number,
"21-jan-2018" number);
insert into Table2 values (102,7,2,5,4);
create table Table3
(custid number,
"date" date,
"data" number);
insert into Table3(custid,"date","data")
(SELECT *
FROM Table2
UNPIVOT
INCLUDE NULLS
(DATA FOR COL IN
(
"01-jan-2018" ,"07-jan-2018","14-jan-2018","21-jan-2018"
)
));
select * from table3;
102 01-JAN-18 7
102 07-JAN-18 2
102 14-JAN-18 5
102 21-JAN-18 4
answered Nov 14 '18 at 16:01
kanagarajkanagaraj
35417
while inserting the data in table3 (May be due to the date field is date and we are inserting the number data. error came--- ORA-01858: a non-numeric character was found where a numeric was expected
– Virender
Nov 14 '18 at 17:37
may be you have number datatype ... I kept as date datatype for Table3 ... I did in Oracle 12c and I have no issues
– kanagaraj
Nov 14 '18 at 18:03
add a comment |
0
Below are steps to achieve this scenario:
create table Table2
(custid number,
"01-jan-2018" number,
"07-jan-2018" number,
"14-jan-2018" number,
"21-jan-2018" number);
insert into Table2 values (102,7,2,5,4);
create table Table3
(custid number,
"date" date,
"data" number);
insert into Table3(custid,"date","data")
(SELECT *
FROM Table2
UNPIVOT
INCLUDE NULLS
(DATA FOR COL IN
(
"01-jan-2018" ,"07-jan-2018","14-jan-2018","21-jan-2018"
)
));
select * from table3;
102 01-JAN-18 7
102 07-JAN-18 2
102 14-JAN-18 5
102 21-JAN-18 4
answered Nov 14 '18 at 16:01
kanagarajkanagaraj
35417
while inserting the data in table3 (May be due to the date field is date and we are inserting the number data. error came--- ORA-01858: a non-numeric character was found where a numeric was expected
– Virender
Nov 14 '18 at 17:37
may be you have number datatype ... I kept as date datatype for Table3 ... I did in Oracle 12c and I have no issues
– kanagaraj
Nov 14 '18 at 18:03
add a comment |
0
0
0
Below are steps to achieve this scenario:
create table Table2
(custid number,
"01-jan-2018" number,
"07-jan-2018" number,
"14-jan-2018" number,
"21-jan-2018" number);
insert into Table2 values (102,7,2,5,4);
create table Table3
(custid number,
"date" date,
"data" number);
insert into Table3(custid,"date","data")
(SELECT *
FROM Table2
UNPIVOT
INCLUDE NULLS
(DATA FOR COL IN
(
"01-jan-2018" ,"07-jan-2018","14-jan-2018","21-jan-2018"
)
));
select * from table3;
102 01-JAN-18 7
102 07-JAN-18 2
102 14-JAN-18 5
102 21-JAN-18 4
answered Nov 14 '18 at 16:01
kanagarajkanagaraj
35417
Below are steps to achieve this scenario:
create table Table2
(custid number,
"01-jan-2018" number,
"07-jan-2018" number,
"14-jan-2018" number,
"21-jan-2018" number);
insert into Table2 values (102,7,2,5,4);
create table Table3
(custid number,
"date" date,
"data" number);
insert into Table3(custid,"date","data")
(SELECT *
FROM Table2
UNPIVOT
INCLUDE NULLS
(DATA FOR COL IN
(
"01-jan-2018" ,"07-jan-2018","14-jan-2018","21-jan-2018"
)
));
select * from table3;
102 01-JAN-18 7
102 07-JAN-18 2
102 14-JAN-18 5
102 21-JAN-18 4
answered Nov 14 '18 at 16:01
kanagarajkanagaraj
35417
answered Nov 14 '18 at 16:01
kanagarajkanagaraj
35417
answered Nov 14 '18 at 16:01
kanagarajkanagaraj
35417
answered Nov 14 '18 at 16:01
kanagarajkanagaraj
35417
35417
while inserting the data in table3 (May be due to the date field is date and we are inserting the number data. error came--- ORA-01858: a non-numeric character was found where a numeric was expected
– Virender
Nov 14 '18 at 17:37
may be you have number datatype ... I kept as date datatype for Table3 ... I did in Oracle 12c and I have no issues
– kanagaraj
Nov 14 '18 at 18:03
add a comment |
while inserting the data in table3 (May be due to the date field is date and we are inserting the number data. error came--- ORA-01858: a non-numeric character was found where a numeric was expected
– Virender
Nov 14 '18 at 17:37
may be you have number datatype ... I kept as date datatype for Table3 ... I did in Oracle 12c and I have no issues
– kanagaraj
Nov 14 '18 at 18:03
while inserting the data in table3 (May be due to the date field is date and we are inserting the number data. error came--- ORA-01858: a non-numeric character was found where a numeric was expected
– Virender
Nov 14 '18 at 17:37
while inserting the data in table3 (May be due to the date field is date and we are inserting the number data. error came--- ORA-01858: a non-numeric character was found where a numeric was expected
– Virender
Nov 14 '18 at 17:37
may be you have number datatype ... I kept as date datatype for Table3 ... I did in Oracle 12c and I have no issues
– kanagaraj
Nov 14 '18 at 18:03
may be you have number datatype ... I kept as date datatype for Table3 ... I did in Oracle 12c and I have no issues
– kanagaraj
Nov 14 '18 at 18:03
add a comment |
0
You may use unpivot keyword as :
create table Table1 as
with Table2(custid,d01_Jan_2018,d07_Jan_2018,d14_Jan_2018,d21_Jan_2018) as
(
select 102, 7, 2, 5, 4 from dual
)
select custid, col1 as "DATE", data from Table2
unpivot
(data for col1 in (d01_Jan_2018, d07_Jan_2018, d14_Jan_2018,d21_Jan_2018));
select * from Table1;
CUSTID DATE DATA
------ ------------ -----
102 D01_JAN_2018 7
102 D07_JAN_2018 2
102 D14_JAN_2018 5
102 D21_JAN_2018 4
SQL Fiddle Demo
answered Nov 14 '18 at 16:03
Barbaros ÖzhanBarbaros Özhan
13.4k71633
In table1 date column is real date column. I tried with same kid of your example.
– Virender
Nov 14 '18 at 17:12
@Virender what'scustidthen, a string type column wrapped in quotes as 'custid' ..?, I supposed that as a column name ...
– Barbaros Özhan
Nov 14 '18 at 17:16
add a comment |
0
You may use unpivot keyword as :
create table Table1 as
with Table2(custid,d01_Jan_2018,d07_Jan_2018,d14_Jan_2018,d21_Jan_2018) as
(
select 102, 7, 2, 5, 4 from dual
)
select custid, col1 as "DATE", data from Table2
unpivot
(data for col1 in (d01_Jan_2018, d07_Jan_2018, d14_Jan_2018,d21_Jan_2018));
select * from Table1;
CUSTID DATE DATA
------ ------------ -----
102 D01_JAN_2018 7
102 D07_JAN_2018 2
102 D14_JAN_2018 5
102 D21_JAN_2018 4
SQL Fiddle Demo
answered Nov 14 '18 at 16:03
Barbaros ÖzhanBarbaros Özhan
13.4k71633
In table1 date column is real date column. I tried with same kid of your example.
– Virender
Nov 14 '18 at 17:12
@Virender what'scustidthen, a string type column wrapped in quotes as 'custid' ..?, I supposed that as a column name ...
– Barbaros Özhan
Nov 14 '18 at 17:16
add a comment |
0
0
0
You may use unpivot keyword as :
create table Table1 as
with Table2(custid,d01_Jan_2018,d07_Jan_2018,d14_Jan_2018,d21_Jan_2018) as
(
select 102, 7, 2, 5, 4 from dual
)
select custid, col1 as "DATE", data from Table2
unpivot
(data for col1 in (d01_Jan_2018, d07_Jan_2018, d14_Jan_2018,d21_Jan_2018));
select * from Table1;
CUSTID DATE DATA
------ ------------ -----
102 D01_JAN_2018 7
102 D07_JAN_2018 2
102 D14_JAN_2018 5
102 D21_JAN_2018 4
SQL Fiddle Demo
answered Nov 14 '18 at 16:03
Barbaros ÖzhanBarbaros Özhan
13.4k71633
You may use unpivot keyword as :
create table Table1 as
with Table2(custid,d01_Jan_2018,d07_Jan_2018,d14_Jan_2018,d21_Jan_2018) as
(
select 102, 7, 2, 5, 4 from dual
)
select custid, col1 as "DATE", data from Table2
unpivot
(data for col1 in (d01_Jan_2018, d07_Jan_2018, d14_Jan_2018,d21_Jan_2018));
select * from Table1;
CUSTID DATE DATA
------ ------------ -----
102 D01_JAN_2018 7
102 D07_JAN_2018 2
102 D14_JAN_2018 5
102 D21_JAN_2018 4
SQL Fiddle Demo
answered Nov 14 '18 at 16:03
Barbaros ÖzhanBarbaros Özhan
13.4k71633
edited Nov 14 '18 at 16:08
answered Nov 14 '18 at 16:03
Barbaros ÖzhanBarbaros Özhan
13.4k71633
answered Nov 14 '18 at 16:03
Barbaros ÖzhanBarbaros Özhan
13.4k71633
answered Nov 14 '18 at 16:03
Barbaros ÖzhanBarbaros Özhan
13.4k71633
13.4k71633
In table1 date column is real date column. I tried with same kid of your example.
– Virender
Nov 14 '18 at 17:12
@Virender what'scustidthen, a string type column wrapped in quotes as 'custid' ..?, I supposed that as a column name ...
– Barbaros Özhan
Nov 14 '18 at 17:16
add a comment |
In table1 date column is real date column. I tried with same kid of your example.
– Virender
Nov 14 '18 at 17:12
@Virender what'scustidthen, a string type column wrapped in quotes as 'custid' ..?, I supposed that as a column name ...
– Barbaros Özhan
Nov 14 '18 at 17:16
In table1 date column is real date column. I tried with same kid of your example.
– Virender
Nov 14 '18 at 17:12
In table1 date column is real date column. I tried with same kid of your example.
– Virender
Nov 14 '18 at 17:12
@Virender what's
custid then, a string type column wrapped in quotes as 'custid' ..?, I supposed that as a column name ...– Barbaros Özhan
Nov 14 '18 at 17:16
@Virender what's
custid then, a string type column wrapped in quotes as 'custid' ..?, I supposed that as a column name ...– Barbaros Özhan
Nov 14 '18 at 17:16
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.
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%2f53303271%2fi-need-to-create-a-oracle-table1-from-another-table2-in-oracle-where-i-need-to%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
Is the column creation in original table having some logic ? Eg Weekly ? Is it hard-coded to be like in your example or the solution has to be dynamic ? Eg. To work for feb,march etc. as well...
– pOrinG
Nov 14 '18 at 15:55