Home » SQL & PL/SQL » SQL & PL/SQL » merge columns from 2 different tables (oracle 10g)
merge columns from 2 different tables [message #638462] Fri, 12 June 2015 04:11 Go to next message
nciteamo
Messages: 27
Registered: October 2014
Junior Member
Hay Guys

i was training my query skill and i thought to merge 2 columns from different tables then i got stuck with the result

this is the query
select decode(id,1,tbs) as dba,
       decode(id,2,tbs) as userr 
from (
select TABLESPACE_NAME tbs, 1 as id from dba_TABLESPACES  
union all
select TABLESPACE_NAME tbs, 2 as id from user_TABLESPACES)


the result is like this below

DBA         |  USERR
-------------------------
SYSTEM      |   NULL
UNDOTBS1    |   NULL
SYSAUX      |   NULL
TEMP        |   NULL
USERS       |   NULL
EXAMPLE     |   NULL
NULL        |   SYSTEM
NULL        |   UNDOTBS1
NULL        |   SYSAUX
NULL        |   TEMP
NULL        |   USERS
NULL        |   EXAMPLE



i wanna make the result like this below, its like pivot function i think..

DBA        |   USERR
------------------------
SYSTEM     |   SYSTEM
UNDOTBS1   |   UNDOTBS1
SYSAUX     |   SYSAUX
TEMP       |   TEMP
USERS      |   USERS
EXAMPLE    |   EXAMPLE


how to make the result like i wanted?

thanks in advance

nciteamo

[Updated on: Fri, 12 June 2015 04:13]

Report message to a moderator

Re: merge columns from 2 different tables [message #638473 is a reply to message #638462] Fri, 12 June 2015 06:09 Go to previous message
bugfox
Messages: 18
Registered: October 2010
Junior Member
select stbs.tablespace_name dba,
utbs.tablespace_name userr
from dba_tablespaces stbs
left join user_tablespaces utbs on utbs.tablespace_name = stbs.tablespace_name
Previous Topic: Duplicate Data
Next Topic: IBM DB2 to Oracle PLSQL Current Day and day code conversion
Goto Forum:
  


Current Time: Mon Mar 18 22:29:39 CDT 2024