Search code examples
sqlssisunpivot

Convert entire data row into column header in SQL or using SSIS

I have a table that is created dynamically from excel and a sample looks like this

F1     F2     F3     F4    F5
ColA   ColB   ColC   Week1 Week2
1      Mango  Fruit  5     6
2      Potato Veg    4     3
3      Grapes Fruit  4     4

In the above example, F1, F2 are the columns of the table. However ColA, ColB are the actual column headers of the data and Week1, Week2 keep changing

I need the above data converted into the format below :

ColA   ColB     ColC       Week      Qty
1      Mango    Fruit      Week1     5
1      Mango    Fruit      Week2     6
2      Potato   Veg        Week1     4
2      Potato   Veg        Week2     3
3      Grapes   Fruit      Week1     4
3      Grapes   Fruit      Week2     4

I generated a unique ID for every Row in my staging table and identified the header and data rows separately. Then I tried to use unpivot in SQL and SSIS and even dynamic SQL. However I'm unable to get the desired output and I'm stuck. Can anyone out there guide me on an approach so that I can take it further to implement a solution?

Solution

  • I created a stored procedure making use of a cursor to iterate through the columns of my first staging table and created another transformed table. This table has actual column headers which were present as data row in my former staging table.

    Then i used SQL unpivot to transpose my data.

    Thanks @Prabhat G for your analysis on this issue.