Lynn's solution is the way to go.

In her solution, basedata is a CTE - effectively a subquery in this case - from which the normalised data is being selected.

If you wanted to do the entire thing in SSIS, that is also possible, though quite advanced, through the use of an asynchronous Script Component. Advanced ... and slower - but possible.