Home » SQL & PL/SQL » SQL & PL/SQL » oracle -rearrange columns values based on value type (12.1.0.2)
| oracle -rearrange columns values based on value type [message #680455] |
Fri, 15 May 2020 07:31  |
 |
ghostman
Messages: 16
Registered: October 2019
|
Junior Member |
|
|
I have three oracle tables :
table_1:
ID some_fields
121 xx
122 xx
123 xx
124 xx
125 xx
create table table_1 as
select 121,'xx' from dual
union select 122,'xx' from dual
union select 123,'xx' from dual
union select 124,'xx' from dual
union select 125,'xx' from dual
table_2:
ID some_fields
221 xx
222 xx
223 xx
224 xx
225 xx
create table table_2 as
select 221,'xx' from dual
union select 222,'xx' from dual
union select 223,'xx' from dual
union select 224,'xx' from dual
union select 225,'xx' from dual
table3:
ID field_1 field_2
1 121 221
2 222 125
3 225 124
4 123 223
5 122 224
create table table_3 as
select 121,'221' from dual
union select 222,'125' from dual
union select 225,'124' from dual
union select 123,'223' from dual
union select 122,'224' from dual
I need to re-arrange table_3 to have all ids of table_1 in field_1 and all ids of table_2 in field_2 , knowing that ids in table_1 and table_2 are unique.
ID field_1 field_2
1 121 221
2 125 222
3 124 225
4 123 223
5 122 224
I'm thinking of creating a view that accomplish my need but not sure how to proceed.
[Updated on: Fri, 15 May 2020 07:33] Report message to a moderator |
|
|
|
| Re: oracle -rearrange columns values based on value type [message #680457 is a reply to message #680455] |
Fri, 15 May 2020 07:43  |
John Watson
Messages: 8927
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Ghostman, can you post code that actually works? This doesn't:orclz> create table table_1 as
2 select 121,'xx' from dual
3 union select 122,'xx' from dual
4 union select 123,'xx' from dual
5 union select 124,'xx' from dual
6 union select 125,'xx' from dual;
create table table_1 as
*
ERROR at line 1:
ORA-00998: must name this expression with a column alias
orclz> |
|
|
|
| Re: oracle -rearrange columns values based on value type [message #680458 is a reply to message #680455] |
Fri, 15 May 2020 07:44 |
 |
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> with
2 table_1 as (
3 select 121 id,'xx' val from dual
4 union select 122,'xx' from dual
5 union select 123,'xx' from dual
6 union select 124,'xx' from dual
7 union select 125,'xx' from dual
8 ),
9 table_2 as (
10 select 221 id,'xx' val from dual
11 union select 222,'xx' from dual
12 union select 223,'xx' from dual
13 union select 224,'xx' from dual
14 union select 225,'xx' from dual
15 union select 226,'xx' from dual
16 )
17 select t1.id, t2.id
18 from (select id, rownum rn from table_1) t1
19 full outer join
20 (select id, rownum rn from table_2) t2
21 on t1.rn=t2.rn
22 /
ID ID
---------- ----------
121 221
122 222
123 223
124 224
125 225
226
6 rows selected. |
|
|
|
|
|
| Re: oracle -rearrange columns values based on value type [message #680462 is a reply to message #680455] |
Fri, 15 May 2020 08:02 |
|
ghostman
Messages: 16
Registered: October 2019
|
Junior Member |
|
|
correcting the DDL:
create table table_1 as
select 121 id ,'xx' some_fields from dual
union select 122 id,'xx' some_fields from dual
union select 123 id,'xx' some_fields from dual
union select 124 id,'xx' some_fields from dual
union select 125 id,'xx' some_fields from dual
create table table_2 as
select 221 id,'xx' some_fields from dual
union select 222 id,'xx 'some_fields from dual
union select 223 id,'xx' some_fields from dual
union select 224 id,'xx' some_fields from dual
union select 225 id,'xx' some_fields from dual
create table table_3 as
select 1 id, 121 field_1,221 field_2 from dual
union select 2 id, 222 field_1,125 field_2 from dual
union select 3 id, 225 field_1,124 field_2 from dual
union select 4 id, 123 field_1,223 field_2 from dual
union select 5 id, 122 field_1,224 field_2 from dual
|
|
|
|
|
|
| Re: oracle -rearrange columns values based on value type [message #680465 is a reply to message #680462] |
Fri, 15 May 2020 08:14 |
|
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:correcting the DDL:
If you want an id before then just add the rn in my query:
SQL> with
2 table_1 as (
3 select 121 id,'xx' val from dual
4 union select 122,'xx' from dual
5 union select 123,'xx' from dual
6 union select 124,'xx' from dual
7 union select 125,'xx' from dual
8 ),
9 table_2 as (
10 select 221 id,'xx' val from dual
11 union select 222,'xx' from dual
12 union select 223,'xx' from dual
13 union select 224,'xx' from dual
14 union select 225,'xx' from dual
15 union select 226,'xx' from dual
16 )
17 select nvl(t1.rn,t2.rn) id, t1.id field_1, t2.id field_2
18 from (select id, rownum rn from table_1) t1
19 full outer join
20 (select id, rownum rn from table_2) t2
21 on t1.rn=t2.rn
22 /
ID FIELD_1 FIELD_2
---------- ---------- ----------
1 121 221
2 122 222
3 123 223
4 124 224
5 125 225
6 226
6 rows selected.
[Updated on: Fri, 15 May 2020 08:14] Report message to a moderator |
|
|
|
|
|
|
|
| Re: oracle -rearrange columns values based on value type [message #680475 is a reply to message #680471] |
Fri, 15 May 2020 13:19 |
|
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
If the hypothesis is correct (table_3 fields are not null and values are in either table_1 or table_2) then you need only one of the 2 later tables (I chose table_1):
SQL> with
2 table_1 as (
3 select 121 id,'xx' val from dual
4 union select 122,'xx' from dual
5 union select 123,'xx' from dual
6 union select 124,'xx' from dual
7 union select 125,'xx' from dual
8 ),
9 /*
10 table_2 as (
11 select 221 id,'xx' val from dual
12 union select 222,'xx' from dual
13 union select 223,'xx' from dual
14 union select 224,'xx' from dual
15 union select 225,'xx' from dual
16 ),
17 */
18 table_3 as (
19 select 121 field_1,'221' field_2 from dual
20 union select 222,'125' from dual
21 union select 225,'124' from dual
22 union select 123,'223' from dual
23 union select 122,'224' from dual
24 )
25 select rownum id,
26 nvl(t1.id, t3.field_2) field_1,
27 nvl2(t1.id, t3.field_2, t3.field_1) field_2
28 from table_3 t3 left outer join table_1 t1
29 on t1.id = t3.field_1
30 /
ID FIELD_1 FIELD_2
---------- ---------- ----------------------------------
1 121 221
2 122 224
3 123 223
4 125 222
5 124 225
5 rows selected. |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Apr 16 04:40:22 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
