CODEWITHSUNDEEP
visual-studio-2010ssis
pete
pete

Reputation: 101

SSIS Pivot data

is it possible to pivot in SSIS, is there a tutorial for this?

Data is coming from excel.

I have a columns called ID, WeightScores and SugarScores

Data looks like (sorry I don't know how to show this in the question properly..in edit mode looks fine)

ID WeightScore  SugarScore
1       34         6
2       18         2

I want to repivot columns in ssis called to make ScoreType and Score

it will look hopefully like this (sorry again I don't know how to show this in the question properly..in edit mode looks fine)

ID ScoreType   Score
1  WeightScore   34
2  WeightScore   18
1  Sugar Score   6
2  Sugar Score   2

please help / ideas team

Upvotes: 0

Views: 128

Answers (1)

KeithL
KeithL

Reputation: 5594

You can load to a staging table and then unpivot it. My favorite method is using a cross apply.

/* creating data sample
declare @t  table(
ID int ,WeightScore int,  SugarScore int)

insert into @t
values
(1,       34,         6)
,(2,       18  ,       2)
*/

select ID,a.*
from @t
cross apply (values('WeightScore',WeightScore),('Sugar Score',SugarScore)) a(ScoreType,Score)

Results:

ID  ScoreType   Score
1   WeightScore 34
1   Sugar Score 6
2   WeightScore 18
2   Sugar Score 2

Upvotes: 1

Related Questions