This is well outside my expertise but there's a very detailed post by Jason here that I think will help you. Although he is looking at combining 2 different tables
I suspect you can adapt for your purposes by thinking of your table as 2 different tables.
Without knowing what your end goal is I don't know whether its relevant but if what you are aiming for is a flattened fact table then you might be better trying to deal with it on import - e.g. the SQL UNPIVOT function can be used here and that will certainly
save you any performance issues that this method could cause.