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)); }