CODEWITHSUNDEEP
oracle-databaseoracle11g
Siva
Siva

Reputation: 9101

Merging two tables to one parent table

I have a table with 2 columns ID, Name, Now I am trying to make a two different data with the same table that will come around 2 new tables but I want both the table rows merged into parent table.

I will explain with sample data:

Parent table:

ID    Name
1      abc
2      def

I am writing a select query as

Select ID, Name||'_First' as a from table

This will give me

ID    Name
1      abc_First
2      def_First

Now my another select query as

I am writing a select query as

Select ID, Name||'_Second' as b from table

This will give me

ID    Name
1      abc_second
2      def_Second

Now I am trying to join both the queries and produce the parent table as

Tried like this:

Select ID,a,b from
(Select ID, Name||'_First' as a from table
Inner join
Select ID, Name||'_Second' as b from table)
on joins here

But this is producing me 3 columnslike

ID    a              b
1      abc_First   abc_second
2      def_First   def_Second

But I need as

ID    Name
    1      abc_First
    2      def_First
    1      abc_second
    2      def_Second

I am stuck at this point.

Upvotes: 0

Views: 43

Answers (1)

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Use union all 

Select ID, Name||'_First' as name from table
    union all
    Select ID, Name||'_Second' as name from table

Upvotes: 1

Related Questions