transposing simple parent-child relationships

colinbashbash

is there a better way of doing this? (against sql-server 2000).

basically i want to export data to excel, and add the children rows as columns.

i know there are many times where you shouldn't do this, but this is on a small enough scale that i'm not too worried about that.

(note, i haven't actually tested this code)
=======

private DataTable test() {
...
DataSet ds = cmd.execute();
string relation = "Discrepancy_Parts";
ds.Relations.Add(relation, ds.Tables[0].Columns["DiscrepancyId"], ds.Tables[1].Columns["DiscrepancyId"]);

ParentChildTranspose(ds.Tables[0], relation, "PartNumber,ReceivedQuantity,PackingListQuantity,DiscrepancyQuantity".Split(",".ToCharArray()));
return ds.Tables[0];
}

private void ParentChildTranspose(DataTable parent, string relation, string[] childColumnsToUse) {
int maxSequence = 0;

foreach (DataRow row in parent.Rows) {
DataRow[] childTable = row.GetChildRows(relation);
while (childTable.Length > maxSequence) {
maxSequence++;
addColumns(parent, childTable[0].Table, maxSequence, childColumnsToUse);
}
int sequence = 0;
foreach (DataRow childRow in childTable) {
sequence++;
foreach (string column in childColumnsToUse) {
row[column + sequence] = childrow[column];
}
}
}
}

private void addColumns(DataTable parent, DataTable childTable, int sequence, string[] childColumnsToUse) {
foreach (string column in childColumnsToUse) {
parent.Columns.Add(column + sequence, childTable.Columns[column].DataType);
}
}