Had a request to load some prices into SQL Server from an excel spreadsheet – unfortunately the data was in a pivoted format without the raw pivot data.

This becomes the pivot description for the pivot value – in the example below I wanted to pivot on a variety of durations and get back the prices so set pivot key value to the duration.So having the spreadsheet as a source I created a table as the output – using the new table from oledb desination – modified the table name and we have a new table of the unpivoted data

Much easier if the spreadsheet contains the original unpivoted data – but this gets you the same results, for this client I also added another data transform task for data from another sheet in the same spreadsheet and finally a transform to merge the data from both sources into their data tables

Have fun with it – it’s easier than loading the data in manually