title: Rule based select with source tracking
body: <p>Here is an example of the sample dataset that I am having trouble writing an efficient SQL:</p>
  <p><a href="https://i.stack.imgur.com/p0HWe.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/p0HWe.png" alt="enter image description here" /></a></p>
  <ol>
  <li><p>There is a target table <code>T1</code> with 5 columns ID (primary key), NAME, CATEGORY, HEIGHT, LINEAGE</p>
  </li>
  <li><p><code>T1</code> gets data from 3 sources - <code>source1, source2, source3</code></p>
  </li>
  <li><p>A map table defines the rule as to which column has to be picked in what order from which source</p>
  </li>
  <li><p>If a source has NULL value for a column, then check the next source to get the value - that's the rule</p>
  </li>
  <li><p>So the values for target table columns based on the rules are as below for ID = 1:</p>
  <pre><code> Name: A12, CATEGORY: T1, HEIGHT: 4, Lineage: S3-S1-S1
  </code></pre>
  <p>The values for target table columns based on the rules are as below for ID = 2:</p>
  <pre><code> NAME: B, CATEGORY: T22, HEIGHT: 5, Lineage: S3-S2-S1
  </code></pre>
  </li>
  <li><p>The logic to merge into target should look like this:</p>
  <pre><code> Merge into Target T
   using (select statement with rank based rules from 3 source tables) on 
  
   when matched then
  
   when not matched then
  </code></pre>
  </li>
  </ol>
  <p><strong>Question</strong>: any suggestions on writing this <code>Merge</code> in an efficient way which also should update the Lineage in the merge?</p>