Search code examples
sql-servert-sqlssisetlunpivot

SSIS ETL Unpivot transformation to T-SQL Unpivot

I am trying to convert some SSIS ETL into sql Server code. Old ETl was written for 2005 and new T-SQL code is being written for 2012. I have the following data being transformed in SSIS ETL:

Source Table: AssetName, MoodyRating, SPRating, FitchRating

Source Data:

FirstAsset, 1 , 2 , 3
SecondAsset, 4, 5, 6

That is being unpivoted to the following:

Target Table: AssetName, RatingSouce, RatingValue

Target Data:

FirstAsset, Moody, 1
FirstAsset, SP, 2
FirstAsset, Fitch, 3
SecondAsset, Moody, 4
SecondAsset, SP, 5
SecondAsset, Fitch, 6

I think I can do this in SQL using the UNPIVOT command but I can not find an example that will inject the RatingSource as part of the unpivot process. Any help would be appreciated.

Solution

  • You can use the UNPIVOT function and then just replace the rating in the column names:

    select AssetName,
  replace(RatingSouce, 'Rating', '') RatingSouce,
  RatingValue
from yourtable
unpivot
(
  RatingValue
  for RatingSouce in ([MoodyRating], [SPRating], [FitchRating])
) unpiv

    See SQL Fiddle with Demo