title: Need to merge 2 rows when 2 variables on 2 columns (total 4 variables) from both rows are the same
body: <p>I need help writing a query that can convert the following table into the second table with SMSS. Basically, in the first table we got a claim processed as primary by Blue Shield Blue Cross for procedure code 99214, and then it was processed by the secondary insurance Medicare. The problem with the report generating the table below is that when we do a sum, it Looks like we got paid 150 out of an allowance of 300 for that claim and procedure code, when in reality we got paid out of the allowance of 150. I will have to merge were Procedure_Code for two rows in a column are the same while the Claim_ID for the same two rows are the same.
  I tried pivoting but I don't think I can get the result that way. I am using the case statements</p>
  <p>This is the code I wrote:</p>
  <pre><code>select 
  
  case when [Primary_Insurance] = [Insurance] then [Insurance] end as 'Primary_Insurance',
  case when [Primary_Insurance] &lt;&gt; [Insurance] then [Insurance]  end as 'Secondary_Insurance',
          [Claim_ID],
          [Procedure_Code],
          [Allowance],
  case when [Primary_Insurance] = [Insurance] then [Insurance_Payment]  end as 'Primary_Payment',
  case when [Primary_Insurance] &lt;&gt; [Insurance] then [Insurance_Payment] end as 'Secondary_Payment'
  
  
  from[dbo].[Sheet1$]
   
  </code></pre>
  <p>I get this:</p>
  <pre><code>Primary_Insurance,Secondary_Insurance,Claim_ID,Procedure_Code,AllowancePrimary_Payment,Secondary_Payment
  
  BlueShieldBlueCross,NULL,12345,99214,150,100,NULL
  
  NULL,Medicare,12345,99214,150,NULL,50
  
  United Healthcare,NULL,23456,93306,250,200,NULL
  </code></pre>
  <p>This approaches to the end result but I still need to combine the first two rows together.</p>
  <p>Sample Table</p>
  <pre class="lang-none prettyprint-override"><code>Primary_Insurance     Insurance           Claim_ID  Procedure_Code  Allowance   Insurance_Payment   
  BlueShieldBlueCross   BlueShieldBlueCross 12345     99214           150         100 
  BlueShieldBlueCross   Medicare            12345     99214           150         50  
  UnitedHealthcare      UnitedHealthcare    23456     93306           250         200 
  </code></pre>
  <p>Desired Result:</p>
  <pre class="lang-none prettyprint-override"><code>Primary               Secondary  Claim_ID   Procedure_Code  Allowance   Primary_Payment Secondary_Payment
  BlueShieldBlueCross   Medicare   12345      99214           150         100             50
  UnitedHealthcare      Null       23456      93306           250         200             Null
  </code></pre>
  <p>Thank you beforehand for the feedback!</p>