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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.