Thank you, I will try these options to see if its working. However I have question that why it causing problem on access denied. isn't SP access works as same...

I was able to get the result based on your answer, but first I had to use OUTER APPLY for FIELD1 and then CROSS APPLY on every other fields (FIELD2,...

These table are not from production database, this is how table are created to hold data, so I am trying to clean and combine data from both tables.