private static DataTable CreatePivotMaster(DataTable input,
DataTable result, string keyColumn, string valueColumn,
GroupSignificant gs, DateTime start)
{
var dt = CreateNewMasterTable();
dt.Rows.Add("Pivot", "PivotGS");
dt.Rows.Add("gs", gs);
dt.Rows.Add("keyColumn", keyColumn);
dt.Rows.Add("valueColumn", valueColumn);
dt.Rows.Add("valueType", input.Columns[valueColumn].DataType.ToString());
dt.Rows.Add("start", start.ToString("s"));
dt.Rows.Add("elapsed", new TimeSpan(DateTime.Now.Ticks - start.Ticks).ToString());
dt.Rows.Add("inputTablename", input.TableName);
dt.Rows.Add("inputRowCount", input.Rows.Count);
dt.Rows.Add("inputColumnCount", input.Columns.Count);
dt.Rows.Add("resultRowCount", result.Rows.Count);
dt.Rows.Add("resultColumnCount", result.Columns.Count);
#region Protect these columns
dt.Columns["ID"].ReadOnly = true;
dt.Columns["Value"].ReadOnly = true;
#endregion
return(dt);
}
/// <summary>
/// remove any subgroup(s). A group is considered a subgroup if all of its entries are contained in another group.
/// </summary>
/// <param name="definition"></param>
/// <param name="gs"></param>
/// <returns></returns>
private static void RemoveSubGroups(ref DataTable definition, GroupSignificant gs)
{
definition.Columns.Add(DEF_DELETE_COLUMN, typeof(bool));
foreach (DataRow dr in definition.Rows)
{
dr[DEF_DELETE_COLUMN] = false;
}
using (var dv = new DataView(definition))
{
dv.Sort = DEF_GROUP_COLUMN + " asc";
foreach (DataRowView baseRow in dv)
{
var baseId = Convert.ToInt32(baseRow[DEF_ID_COLUMN]);
var baseGroup = FormatGroup(baseRow[DEF_GROUP_COLUMN].ToString(), gs);
foreach (DataRowView keyRow in dv)
{
var keyId = Convert.ToInt32(keyRow[DEF_ID_COLUMN]);
var keyGroup = FormatGroup(keyRow[DEF_GROUP_COLUMN].ToString(), gs);
// don't compare to self
if (keyId == baseId)
{
continue;
}
if (baseGroup == keyGroup)
{
continue;
}
if (Convert.ToBoolean(keyRow[DEF_DELETE_COLUMN]))
{
continue;
}
if (keyGroup.StartsWith(baseGroup))
{
keyRow[DEF_DELETE_COLUMN] = true;
}
}
}
dv.RowFilter = DEF_DELETE_COLUMN + " = false";
var newTable = dv.ToTable();
//newTable.Columns.Remove(DEF_DELETE_COLUMN);
definition = newTable;
}
}
/// <summary>
/// Format the group name based on the group significant enum
/// </summary>
/// <param name="value"></param>
/// <param name="gs"></param>
/// <returns></returns>
private static string FormatGroup(string value, GroupSignificant gs)
{
if (string.IsNullOrEmpty(value))
{
return(string.Empty);
}
switch (gs)
{
case GroupSignificant.RightMost:
return(StringUtil.Reverse(value));
case GroupSignificant.LeftMost:
return(value);
default:
throw new ArgumentOutOfRangeException("gs=" + gs);
}
}
private DataTable CreateDefinition(DataTable dt, string columnName, GroupSignificant gs)
{
var result = CreateNewDefinitionTable();
#region Perform Grouping
using (var dv = new DataView(dt))
{
foreach (DataRow dr in dt.Rows)
{
string v = StringUtil.TrimWhitespace(dr[columnName].ToString());
if (string.IsNullOrEmpty(v))
{
continue;
}
string group = string.Empty;
#region Set the Stem using Right-Most or Left-Most
switch (gs)
{
case GroupSignificant.RightMost:
{
int startPos = v.Length - 1;
do
{
string testStem = v.Substring(startPos);
dv.RowFilter = string.Format("{0} like '%{1}'", columnName, testStem);
if (dv.Count <= 1)
{
break;
}
group = testStem; // NoTrailingNumber(testStem);
startPos--;
} while (true);
// use the last good stem
dv.RowFilter = string.Format("{0} like '%{1}'", columnName, group);
}
break;
case GroupSignificant.LeftMost:
{
int stopPos = 0;
while (++stopPos < v.Length)
{
string testStem = v.Substring(0, stopPos);
dv.RowFilter = string.Format("{0} like '{1}%'", columnName, testStem);
if (dv.Count <= 1)
{
break;
}
group = testStem; // NoTrailingNumber(testStem);
}
// use the last good stem
dv.RowFilter = string.Format("{0} like '{1}%'", columnName, group);
}
break;
default:
throw new ArgumentOutOfRangeException("gs=" + gs);
}
#endregion
if (string.IsNullOrEmpty(group) || group.Length == 1)
{
var dr2 = result.NewRow();
dr2[DEF_GROUP_COLUMN] = v;
dr2[DEF_ENTRY_COLUMN] = v;
result.Rows.Add(dr2);
continue;
}
if (DataViewFilter(result, string.Format("{0} = '{1}'", DEF_GROUP_COLUMN, group)).Count > 0)
{
continue;
}
#region Add to result
for (int i = 0; i < dv.Count; i++)
{
var dr2 = result.NewRow();
dr2[DEF_GROUP_COLUMN] = group;
dr2[DEF_ENTRY_COLUMN] = StringUtil.TrimWhitespace(dv[i][columnName].ToString());
result.Rows.Add(dr2);
}
#endregion
}
}
#endregion
RemoveSubGroups(ref result, gs);
return(result);
}
/// <summary>
/// Pivot a table using key column, by splitting its value using the right-most or left-most significant algorithm
/// </summary>
/// <param name="dt"></param>
/// <param name="valueColumn"></param>
/// <param name="keyColumn"></param>
/// <param name="gs"></param>
/// <returns></returns>
public static DataSet Pivot(DataTable dt, string valueColumn, string keyColumn, GroupSignificant gs)
{
var start = DateTime.Now;
var result = CreateNewPivotTable();
var definition = CreateDefinition(dt, keyColumn, gs);
PivotInternal(dt, definition, result, new string[] { keyColumn }, valueColumn);
var master = CreatePivotMaster(dt, result, keyColumn, valueColumn, gs, start);
return(CreateNewPivotDataSet(dt, definition, master, result));
}
