How do I pivot query using UNION ALL

Question

I have following table structure & below is the Query i am executing :-

Following are the tables :-

First Table :-

Second Table :-

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

Result i am getting :-

I also want Records from the Second table i mentioned above. So In my final Result Columns SUBFLD36 SUBFLD37 & House_Rent will be added.

As i am new to Pivot, Help me to modify above query to get expected results.

Answer 1

You can try this:

SELECT Code, Fname, OTHINC1, OTHINC2, SUBFLD36, SUBFLD37, House_Rent
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'
      UNION
      SELECT a.FieldName
            ,a.FieldValue AS YearlyValue
            ,a.EmployeeCode AS Code
            ,b.Fname
      FROM SecondTable AS a
      INNER JOIN [EmployeeDetail] AS b
         ON a.EmployeeId = b.Id
      WHERE EntryDate = '2014-12-01') x
      PIVOT (MAX(YearlyValue) FOR FieldName IN (OTHINC1, OTHINC2, SUBFLD36, SUBFLD37, House_Rent)) p

Answer 2

Consider you are inserting the result after your join to a temporary table which is the below

Declare a variable for getting the columns to pivot

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + [FIELD NAME] + ']', 
               '[' + [FIELD NAME] + ']')
               FROM    (SELECT DISTINCT [FIELD NAME] FROM #TEMP) PV  
               ORDER BY [FIELD NAME]

Now pivot it

DECLARE @query NVARCHAR(MAX)
    SET @query = '           
                  SELECT * FROM 
                 (
                     SELECT * FROM #TEMP
                 ) x
                 PIVOT 
                 (
                     MAX([YEARLY VALUE])
                     FOR [FIELD NAME] IN (' + @cols + ')
                ) p      

                '     
    EXEC SP_EXECUTESQL @query

Click here to view the result

RESULT