Thank you Luis .. apologies for the late reply. I had no internet access overnight but back in the office now.

I have done this:

Unpivot data

Left Join to Metadata table

Pivot data

it worked.

Many thanks for your help on this!

As for the design I am totally against it but there are times when you just have to build a sample solution to prove your point and this is one of them!