Thanks Jeff, I was just after the idea. I have got the code working after Luis comments.
Thank you to all for replying!
aarionsql (4/2/2014)
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!