Pivot or Unpivot a dataset using Excel, SSIS or SQL Server

0
votes

I have an Excel file with various market indexes, dates, and values. In this file there is a single column to the left representing the market index names followed by many date columns to the right. I can perform this pivot in Excel, SSIS or SQL Server. I have the most recent versions of each program. I don't want to simply copy and paste special transpose in Excel. I would like the solution to be automated as possible. I suspect loading this data into SSMS and using SQL would be the easiest. I can change the format of the dates if needed.

I have used pivot in both SSIS and SSMS but always with less columns than this tasks requires and am not sure how to approach it in a way that will allow for the large amount of columns and potential for the number of columns (dates) to vary. Perhaps this requires dynamic SQL. The dates which comprise the bulk of the columns can potentially extend 100 rows or more. Note there may be null values if an index didn't have a value on a given day.

Input data

Input

Here is the desired output format.

Output

Here is the data loaded into SSMS 2012. The dates become the column headers. Same goal of transposing the dates and index names.

enter image description here

1
sql-serverssisetl

1 Answers

1
votes

I would use the Excel Power Query Add-In for this. It has Pivot and Unpivot commands that you can use. The Power Query implementations of both commands are dynamic i.e. they adjust to variations in the input data.

For your scenario I would first select Name and Unpivot all other columns. That will transform each date column and value into a row. Then I would Pivot on the Name column, which will generate columns for each of your Name values.

To automate this process, you just need a few lines of VBA code or script code to open, refresh and save the Excel file (including Power Queries). There are lots of options for this, e.g.

http://southbaydba.com/2013/09/10/part-5-power-query-api-refreshing-data-indeed/