CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
omdurg
omdurg

Reputation: 330

SQL Table based on a pattern

I have a Table like:

source    target              
jan       feb                               
mar       apr                 
jun                       
feb       aug                                            
apr       jul                                            
oct       dec                     
aug       nov       
dec       may

The output (where I want to create a new_target column):

source    target    new_target              
jan       feb       aug                        
mar       apr       jul                  
jun                              
feb       aug       nov                                     
apr       jul                                                  
oct       dec       may              
aug       nov       
dec       may

The aim is to create new_targetcolumn based on a logic like - for example, jan in source has value feb in target. This in turn, feb in source has a value aug in target, and so on aug has nov in target column So the new_target column will have 3rd value: i.e (trace followed between source and target jan->feb->aug->nov, since aug is 3rd value, it is the output in new_target column)

Upvotes: 0

Views: 32

Answers (2)

Kurt Kline
Kurt Kline

Reputation: 2069

Try this: 

select  m1.source, 
        m1.target, 
        m2.target as new_target
from mytable m1
left join mytable m2 on 
  m1.target = m2.source

The left join will maintain all rows from the original table, while adding values to the new_target column if there is a match.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This looks like a left join:

select t.*, tnext.target
from t left join
     t tnext
     on t.target = t.next.source

Upvotes: 1

Related Questions