private static PivotedTable Pivot(IDataReader dataValues, string keyColumn, string pivotNameColumn, string pivotValueColumn)
{
PivotedTable tableResults = new PivotedTable();
int pValIndex, pNameIndex;
string sColumnName;
bool isFirstRow = true;
tableResults.IndexOfLastNonPivotColumn.Equals(dataValues.FieldCount - 3);
// Add non-pivot columns to the data table:
pValIndex = dataValues.GetOrdinal(pivotValueColumn);
pNameIndex = dataValues.GetOrdinal(pivotNameColumn);
for (int colIndex = 0; colIndex <= dataValues.FieldCount - 1; colIndex++)
{
if (colIndex != pValIndex && colIndex != pNameIndex)
{
tableResults.Columns.Add(dataValues.GetName(colIndex), dataValues.GetFieldType(colIndex));
}
}
// Now, fill up the table with the data:
DataRow r = null;
string LastKey = "//dummy//";
while (dataValues.Read())
{
// see if we need to start a new row
if (dataValues[keyColumn].ToString() != LastKey)
{
// if this isn't the very first row, we need to add the last one to the table
if (!isFirstRow)
{
tableResults.Rows.Add(r);
}
r = tableResults.NewRow();
isFirstRow = false;
// Add all non-pivot column values to the new row:
for (int i = 0; i <= tableResults.IndexOfLastNonPivotColumn; i++)
{
r[i] = dataValues[tableResults.Columns[i].ColumnName];
}
LastKey = dataValues[keyColumn].ToString();
}
// assign the pivot values to the proper column; add new columns if needed:
sColumnName = dataValues[pNameIndex].ToString();
if (sColumnName != "")
{
if (!tableResults.Columns.Contains(sColumnName))
{
DataColumn c = tableResults.Columns.Add(sColumnName, dataValues.GetFieldType(pValIndex));
// set the index so that it is sorted properly:
int newOrdinal = c.Ordinal;
for (int i = newOrdinal - 1; i >= dataValues.FieldCount - 2; i--)
{
if (c.ColumnName.CompareTo(tableResults.Columns[i].ColumnName) < 0)
{
newOrdinal = i;
}
}
c.SetOrdinal(newOrdinal);
}
r[sColumnName] = dataValues[pValIndex];
}
}
// add that final row to the datatable:
if (r != null)
{
tableResults.Rows.Add(r);
}
// Add in zeroes
for (int row = 0; row < tableResults.Rows.Count; row++)
{
for (int col = Math.Max(0, tableResults.IndexOfLastNonPivotColumn - 1); col < tableResults.Columns.Count; col++)
{
if (tableResults.Rows[row][col].ToString() == "")
{
tableResults.Rows[row][col] = 0;
}
}
}
dataValues.NextResult();
return(tableResults);
}
private static PivotedTable Pivot(IDataReader dataValues, string keyColumn, string pivotNameColumn, string pivotValueColumn)
{
PivotedTable tableResults = new PivotedTable();
int pValIndex, pNameIndex;
string sColumnName;
bool isFirstRow = true;
tableResults.IndexOfLastNonPivotColumn.Equals(dataValues.FieldCount - 3);
// Add non-pivot columns to the data table:
pValIndex = dataValues.GetOrdinal(pivotValueColumn);
pNameIndex = dataValues.GetOrdinal(pivotNameColumn);
for (int colIndex = 0; colIndex <= dataValues.FieldCount - 1; colIndex++)
if (colIndex != pValIndex && colIndex != pNameIndex)
tableResults.Columns.Add(dataValues.GetName(colIndex), dataValues.GetFieldType(colIndex));
// Now, fill up the table with the data:
DataRow r = null;
string LastKey = "//dummy//";
while (dataValues.Read())
{
// see if we need to start a new row
if (dataValues[keyColumn].ToString() != LastKey)
{
// if this isn't the very first row, we need to add the last one to the table
if (!isFirstRow)
tableResults.Rows.Add(r);
r = tableResults.NewRow();
isFirstRow = false;
// Add all non-pivot column values to the new row:
for (int i = 0; i <= tableResults.IndexOfLastNonPivotColumn; i++)
r[i] = dataValues[tableResults.Columns[i].ColumnName];
LastKey = dataValues[keyColumn].ToString();
}
// assign the pivot values to the proper column; add new columns if needed:
sColumnName = dataValues[pNameIndex].ToString();
if (sColumnName != "")
{
if (!tableResults.Columns.Contains(sColumnName))
{
DataColumn c = tableResults.Columns.Add(sColumnName, dataValues.GetFieldType(pValIndex));
// set the index so that it is sorted properly:
int newOrdinal = c.Ordinal;
for (int i = newOrdinal - 1; i >= dataValues.FieldCount - 2; i--)
if (c.ColumnName.CompareTo(tableResults.Columns[i].ColumnName) < 0)
newOrdinal = i;
c.SetOrdinal(newOrdinal);
}
r[sColumnName] = dataValues[pValIndex];
}
}
// add that final row to the datatable:
if (r != null)
tableResults.Rows.Add(r);
// Add in zeroes
for (int row = 0; row < tableResults.Rows.Count; row++)
for (int col = Math.Max(0, tableResults.IndexOfLastNonPivotColumn - 1); col < tableResults.Columns.Count; col++)
if (tableResults.Rows[row][col].ToString() == "")
tableResults.Rows[row][col] = 0;
dataValues.NextResult();
return tableResults;
}
