SELECT T1.*,ISNULL(T2.Desc,T1.Desc) AS YOUNEED

FROM [YOUTABLE] T1 LEFT JOIN [YOUTABLE] T2

ON T1.Reference_PK = T2.PK

For your column always fix,you can use follow code:

declare @sql nvarchar(max)

select @sql=isnull(@sql+',','')+''''+EmpName+'''' from Sample

select @sql = '''EmpName'','+@sql+' union select ''Empsalary'','

select @sql=isnull(@sql,'')+''''+convert(varchar(20),Empsalary)+''',' from Sample

select @sql = stuff(@sql,len(@sql),1,'')+' union select ''EmpDesignation'','

select @sql=isnull(@sql,'')+''''+convert(varchar(60),EmpDesignation)+''','...