I have a datatable with 3 columnas 'department' , 'team', 'member', and need to transform it so that rows with idential 'department' and 'team' column would merge into 1 row, with 'member' column contain comma seperated data.

Original
|marketing | team A | dickson |
|marketing | team A |bonnie |
|accounting| team A |edison |
|accounting| teamB |jason |

After transformation
|marketing | team A |dickson,bonnie |
|accounting| team A |edison |
|accounting| teamB |jason |

I dont think, there is direct way to do that,
You have to change your SQL Query
or
Create new DataTable fill it with new condition, like:

DataTable newDataTable = oldDataTable.Clone();
foreach(DataRow dr in oldDataTable.Rows)
{
DataRow[] drsResult = newDataTable.Select("/*your criteria idential 'department' and 'team' column*/");
if(drsResult.length >0)
{
drsResult[0] = drsResult +" , "+dr["member"];
}
else
{
newDataTable.ImportRow(dr);

}
}

I dont think, there is direct way to do that,
You have to change your SQL Query
or
Create new DataTable fill it with new condition, like:

DataTable newDataTable = oldDataTable.Clone();
foreach(DataRow dr in oldDataTable.Rows)
{
DataRow[] drsResult = newDataTable.Select("/*your criteria idential 'department' and 'team' column*/");
if(drsResult.length >0)
{
drsResult[0] = drsResult +" , "+dr["member"];
}
else
{
newDataTable.ImportRow(dr);

}
}

Hi NareshG,

Thanks for the answer. So there is not quick way for doing this transformation. I finally wrote the below routine.

private DataTable TransformColumn(DataTable dtOriginal)
{
int counter = 0;
string deleteIdentifier = "!ToBeDeleted";

DataTable dtTransform = dtOriginal.Copy();

foreach (DataRow dr in dtTransform.Rows)
{
StringBuilder member= new StringBuilder();
foreach (DataRow dr2 in dtTransform.Rows)
{
if (dr["department"].ToString() == dr2["department"].ToString() && dr["team"].ToString() == dr2["team"].ToString())
{

if (dr["member"].ToString() != dr2["member"].ToString())
{
if (!dr2["member"].ToString().Contains(deleteIdentifier) && !dr["member"].ToString().Contains(deleteIdentifier))
{
accessRight.Append(",").Append(dr2["member"]);
dr2["member"] = deleteIdentifier + counter.ToString();
counter = counter + 1;
}

}
}
}
dr["member"] = dr["member"].ToString() + member.ToString();
}

for (int i = dtTransform.Rows.Count - 1; i >= 0; i--)
{
DataRow dr = dtTransform.Rows[i];
if (dr["member"].ToString().Contains("member"))
{
dtTransform.Rows.Remove(dr);
}
}

return dtTransform;
}