How to unpivot the table with column Names

Question

I have two tables with similar records. I have the result as follows:

using the following query

Select
     New.ParentId                   
    ,New.FatherFirstName            
    ,New.FatherLastName             
from ParentsUpdationDetails New
where New.parentId=15999

union all

select
     Old.ParentId                   
    ,Old.FatherFirstName            
    ,Old.FatherLastName             
from parents Old 
where Old.parentId=15999

I need to unpivot and want the following output:

Answer 1

you should be able to handle this using CROSS APPLY with a few Table Value Constructors and combining them using INNER JOIN

when you use Cross Apply with (VALUES (Field1), (Field2)) it acts similar to UNPIVOT in that you get a row for each Field you list in your TVC

SELECT  ca.Field, ca.New, lj.Old
FROM    ParentsUpdationDetails new 
        CROSS APPLY ( 
            VALUES ('ParentID',         CAST(ParentID AS VARCHAR)), -- All datatypes must match 
                   ('FatherFirstName',  FatherFirstName), 
                   ('FatherLastName',   FatherLastName)
        ) ca(Field, New)
        INNER JOIN  (
            SELECT  ParentID, Field, Old
            FROM    Parents old 
                    CROSS APPLY ( 
                        VALUES ('ParentID',         CAST(ParentID AS VARCHAR)),  -- All datatypes must match
                               ('FatherFirstName',  FatherFirstName), 
                               ('FatherLastName',   FatherLastName)
                    ) ca(Field, Old)
        ) lj ON new.ParentID = lj.ParentID AND ca.Field = lj.Field
WHERE   new.ParentID = 15999

be aware that you will be converting non varchar datatypes to varchars in order for this to work