title: How do I pivot query using UNION ALL
body: <p>I have following table structure &amp; below is the Query i am executing :-</p>
  
  <p>Following are the tables :-  </p>
  
  <p><strong>First Table :-</strong> </p>
  
  <p><img src="https://i.stack.imgur.com/vY4HI.png" alt="enter image description here"></p>
  
  <p><strong>Second Table :-</strong></p>
  
  <p><img src="https://i.stack.imgur.com/Rxqdy.png" alt="enter image description here"></p>
  
  <pre><code>SELECT Code, Fname, OTHINC1, OTHINC2
  FROM (SELECT a.FieldName
              ,a.YearlyValue
              ,b.Code
              ,b.Fname
        FROM [TaxOtherIncome] AS a
        INNER JOIN [EmployeeDetail] AS b
           ON a.EmployeeId = b.Id
        WHERE a.EntryDate = '2014-12-01') x
        PIVOT (MAX(YearlyValue) FOR FieldName IN (OTHINC1,OTHINC2)) p
  </code></pre>
  
  <p>Result i am getting :-</p>
  
  <p><img src="https://i.stack.imgur.com/pw6YE.png" alt="enter image description here"></p>
  
  <p>I also want Records from the Second table i mentioned above. So In my final Result Columns <code>SUBFLD36</code> 
  <code>SUBFLD37</code>  &amp; <code>House_Rent</code>  will be added.</p>
  
  <p>As i am new to Pivot, Help me to modify above query to get expected results.</p>