Pivot DataSource Columns into Rows

Using Dynamic Columns to convert your Columns into new rows

Here we have a Data Transformation method to pivot normal row data so that the columns in the original data source can be used to create multiple new rows.

Source Data

Taking this simple source data we need to switch it around so that the row columns become new rows.

Pivot source Data

Result Data

The result were looking for here is have 3 columns ID the original source ID, MetaKey the name of the value and MetaValue the actual value from the row.

We also want to add additional rows with fixed values.

Pivot Result Data

Dynamic Columns

Using Dynamic columns we're going to capture the original row values, add new rows and then remove the original row.

partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal
{
    //Our Dynamic Columns
    public string MetaKey { get; set; }
    public string MetaValue { get; set; }
	
    private bool processingRow = false;
	
    public override bool BeginRow()
    {		
	    //If we're currently adding our new rows return true to include them in the results.
	    if(processingRow) return true;
		
	    processingRow = true;
		
	    //Capture this row data
	    var nickname = Nickname;
	    var first_name = FirstName;
	    var last_name = LastName;
		
	    //Add New Rows
	    Table.Rows.Add(AddRow(ID, "nickname", nickname));
	    Table.Rows.Add(AddRow(ID, "first_name", first_name));
	    Table.Rows.Add(AddRow(ID, "last_name", last_name));
	    Table.Rows.Add(AddRow(ID, "description", ""));
	    Table.Rows.Add(AddRow(ID, "rich_editing", "true"));
	    Table.Rows.Add(AddRow(ID, "syntax_highlighting", "true"));
	    Table.Rows.Add(AddRow(ID, "comment_shortcuts", "false"));
		
	    processingRow = false;
		
	    //Remove the original row from the results
	    return false;
    }
	
    private DataTableStoreRow AddRow(string ID, string key, string value) 
    {
	    //Create new Row
	    var row =  Table.NewRow();	
	    row["ID"] = ID;		
		
	    //Set Dynamic Column Values
	    MetaKey = key;
	    MetaValue = value;
		
	    return row;
    }
}

There is a slight variation to this when the source connector uses an internal identifier. The more advanced connectors such as Dynamics, Salesforce, AD, Podio and SharePoint require row identifiers to be set for each row.

For example to use this with Dynamics CRM systemuser entity would require obtaining the row identifier and adding the rows with the AddWithIdentifier method.


    // Get the source identifier
    var id = Row.GetIdentifier<Guid>();

    var nickname = domainname;
    var first_name = firstname;
    var last_name = lastname;
		
    Table.Rows.AddWithIdentifier(AddRow(systemuserid.ToString(), "nickname", nickname), id);
    Table.Rows.AddWithIdentifier(AddRow(systemuserid.ToString(), "first_name", first_name), id);
    Table.Rows.AddWithIdentifier(AddRow(systemuserid.ToString(), "last_name", last_name), id);
    Table.Rows.AddWithIdentifier(AddRow(systemuserid.ToString(), "description", ""), id);
    Table.Rows.AddWithIdentifier(AddRow(systemuserid.ToString(), "rich_editing", "true"), id);
    Table.Rows.AddWithIdentifier(AddRow(systemuserid.ToString(), "syntax_highlighting", "true"), id);
    Table.Rows.AddWithIdentifier(AddRow(systemuserid.ToString(), "comment_shortcuts", "false"), id);

Pivot DataSource Columns into Rows

Source Data

Result Data

Dynamic Columns

Data Sync Project

Products

Help & Support

Store