This is why I switched primarily to Python for all of my ETL so we can do things like this on top of easily doing dynamic pivots and headers. At this stage though, been moving away from headers all together though.
What do you use to co-ordinate the transformations? Apache Airflow or similar?