title: Find matching records based on dynamic columns
body: <p>I have a list of pets:<br>
  <a href="https://i.stack.imgur.com/G7OdD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/G7OdD.png" alt="Pets"></a></p>
  
  <p>And I need to find a correct owner for each of the pet from Owner table  </p>
  
  <p><a href="https://i.stack.imgur.com/WfYZ6.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/WfYZ6.png" alt="Owners"></a></p>
  
  <p>In order to correctly match each pet to an owner I need to use a special matching table that looks like this: </p>
  
  <p><a href="https://i.stack.imgur.com/HRfs0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HRfs0.png" alt="Matching"></a></p>
  
  <p>So, for pet with PetID=2 I need to find an owner that has a matched based on three fields:  </p>
  
  <pre><code>    Pet.Zip = Owner.Zip 
      and Pet.OwnerName = Owner.Name 
      and Pet.Document = Owner.Document
  </code></pre>
  
  <p>In our example, it will work like this:   </p>
  
  <pre><code> select top 1 OwnerID from owners
           where Zip = 23456 
           and Name = 'Alex' 
           and Document = 'a.csv'
  </code></pre>
  
  <p>if OwnerID is not found I then need to match based on 2 fields (Not using field with the highest priority)</p>
  
  <p>In our example: </p>
  
  <pre><code> select top 1 OwnerID from owners where
               Name = 'Alex' 
               and Document = 'a.csv'
  </code></pre>
  
  <p>Since no record is found I we then need to match on less fields. In our example: </p>
  
  <pre><code>select top 1 OwnerID from owners where Document = 'a.csv'
  </code></pre>
  
  <p>Now, we found an owner with OwnerID = 6.</p>
  
  <p>Now we need to update pet with ownerID = 6 and then we can process next pet.</p>
  
  <p>The only way that I can do this right now involves a loop or a cursor + dynamic SQL.</p>
  
  <p>Is it possible to achieve this without loops+dynamic sql? Maybe STUFF + Pivot somehow?</p>
  
  <p>sql fiddle: <a href="http://sqlfiddle.com/#!18/10982/1/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!18/10982/1/0</a></p>
  
  <p>Sample data:</p>
  
  <pre><code>create table  temp_builder
  (
      PetID int not null,
      Field varchar(30) not null,
      MatchTo varchar(30) not null,
      Priority int not null
  )
  
  insert into temp_builder values
  (1,'Address', 'Addr',4),
  (1,'Zip', 'Zip', 3),
  (1,'Country', 'Country', 2),
  (1,'OwnerName', 'Name',1),
  (2,'Zip', 'Zip',3),
  (2,'OwnerName','Name', 2),
  (2,'Document', 'Document', 1),
  (3,'Country', 'Country', 1)
  
  
  create table temp_pets
  (
      PetID int null,
      Address varchar(100) null,
      Zip int null,
      Country varchar(100) null,
      Document varchar(100) null,
      OwnerName varchar(100) null,
      OwnerID int null,
      Field1 bit null,
      Field2 bit null
  )
  
  insert into temp_pets values
  (1, '123 5th st', 12345, 'US', 'test.csv', 'John', NULL, NULL, NULL),
  (2, '234 6th st', 23456, 'US', 'a.csv', 'Alex', NULL, NULL, NULL),
  (3, '345 7th st', 34567, 'US', 'b.csv', 'Mike', NULL, NULL, NULL)
  
  create table temp_owners
  (
      OwnerID int null,
      Addr varchar(100) null,
      Zip int null,
      Country varchar(100) null,
      Document varchar(100) null,
      Name varchar(100) null,
      OtherField bit null,
      OtherField2 bit null,
  )
  
  insert into temp_owners values
  (1, '456 8th st',  45678, 'US', 'c.csv', 'Mike',  NULL, NULL),
  (2, '678 9th st',  45678, 'US', 'b.csv', 'John',  NULL, NULL),
  (3, '890 10th st', 45678, 'US', 'b.csv', 'Alex',  NULL, NULL),
  (4, '901 11th st', 23456, 'US', 'b.csv', 'Alex',  NULL, NULL),
  (5, '234 5th st',  12345, 'US', 'b.csv', 'John',  NULL, NULL),
  (6, '123 5th st',  45678, 'US', 'a.csv', 'John',  NULL, NULL)
  </code></pre>
  
  <p>Edit: I'm overwhelmed by a number of great suggestions and responses. I've tested them and many worked well for me. Unfortunately, I can only award bounty to one solution.</p>