CODEWITHSUNDEEP
sqlssisunpivot
Mekha
Mekha

Reputation: 73

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?

Upvotes: 1

Views: 733

Answers (2)

Mekha
Mekha

Reputation: 73

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.

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

Unpivot shouldn't be a problem atleast. Try this:

create table tbl(F1 varchar(20),F2 varchar(20),F3 varchar(20),F4 varchar(20),F5 varchar(20))
insert into tbl values
('ColA','ColB','ColC','Week1','Week2'),
('1','Mango'  ,'Fruit','5','6'),
('2','Potato' ,'Veg','4','3'),
('3','Grapes' ,'Fruit','4','4')


SELECT F1 as ColA, F2 as ColB, F3 as ColC,week_no, quantity 
FROM
    (SELECT F1, F2, F3, F4, F5
     FROM tbl) iq
    UNPIVOT
    (quantity FOR Week_no IN(F4, F5)
    ) As wk1
WHERE F1 not in ('ColA')  //to filter out header

output:

ColA   ColB       ColC        week_no    quantity
-----  -------    -------     --------   -----------
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

Upvotes: 1

Related Questions