I have a question related to Pivot transformation. I have the transformation working but having difficult to adjust the idea on how to load a dimension. I am trying to extract the data from a table that is in Key value Pair format and all of the Keyvalue has a key that joins to the main table. The effort i am trying to make here is to load dimension. All the dimension attribute that needs to be loaded are the Name values in Keyvalue. to avoid the setbased approach i am using Checksum to improve efficiency of the data loading process. Because I need to load the distinct values , i can't consider the Key column that joins to load in main table. But when i pivot the unique value that identifies the row is the Keycolumn. Using those value will not generate the unique rows output. Thoughts on how to solve the problem ?

Have you sorted the rows by KeyColumn before using the Pivot? If you don't, you'll get several rows with the same KeyColumn, but with different pivoted columns filled in. Talk to me now on

Yes. I did sort keycolumn before loading.

Can you post a sample of: What your input data looks likeWhat your current output data from the Pivot looks likeWhat you want the output from the Pivot to look like Talk to me now on

Todd, Here is the Image for all the input data type, Current out put and expected out put. I am trying to load dimension so i expect to load distinct values in Dimension. Anythoughts would be helpful ?

The problem is that "id" column, and the fact your source has two different instances of the "343" data. How do you intend to solve that problem? In your sample, the other two columns (ContentName and ContentType) happen to be equivalent for ContentID 343. Is this always expected to be the case? If it is, then place a Sort component before the Pivot. Sort on ContentID, and include ContentName and ContentType, but do NOT include id. Reconfigure the Pivot to work on the ContentID column instead of the id column. Talk to me now on

ContentName will be same always and Content type where the instance is same. Atleast i hope. if not atleast i hope to pick the Max Value. I will need to give a try on the alternative.. But any suggestion how can this be solved. The idea of loading unique Content Id is so my fact has the proper reference to one single value.

Please read the second paragraph of my last post. Talk to me now on

Hi, I'm not sure if your expcted output example - 345 - CDC Report - XML should be 345 - CDC Report - EXCEL (According to your current input)? My testing data are the same with your testing data - 1 ContentID 343 1 ContentName ABC Report 1 ContentType XML 2 ContentID 345 2 ContentName CDC Report 2 ContentType EXCEL 3 ContentID 343 3 ContentName ABC Report 3 ContentType XML Please see the below figure that I made a test for your testing data I hope this is what you need. Steps - 1. for OLE DB Source, I made a SQL query like this - SELECT ID, KeyName, KeyValue FROM( SELECT ROW_NUMBER() OVER (PARTITION BY KeyName, KeyValue ORDER BY ID, KeyName )AS 'BID', ID, KeyName, KeyValue FROM dbo.Content )AS PTABLE WHERE BID = 1 ORDER BY ID, KeyName Results from this SQL as a source before 'Pivot' 1 ContentID 343 1 ContentName ABC Report 1 ContentType XML 2 ContentID 345 2 ContentName CDC Report 2 ContentType EXCEL 2. In 'Pivot' Configuration for input columns: Input Columns PivotUsage ID 1 KeyName 2 KeyValue 3 for output columns: Output Columns PivotKeyValue SourceColumn ID ID ID (Input Columns)'s LineageID ContentID ContentID Key Value (Input Columns)'s LineageID ContentName ContentName Key Value (Input Columns)'s LineageID ContentType ContentType Key Value (Input Columns)'s LineageID Then I get the results like the first figure shows Hope it helps, vote if it's helpful. Simon