/// <summary> /// The classical approach. The rotation is made based on "pco" value /// </summary> /// <param name="oldTable"></param> /// <param name="pco"></param> /// <returns></returns> public static DataTable RotateTable(DataTable oldTable, int pco = 0) { var newTable = new DataTable(); if (pco > oldTable.Columns.Count) { pco = 0; } newTable.Columns.Add(oldTable.Columns[pco].ColumnName); int counter = 0; foreach (DataRow row in oldTable.Rows) { string newName = row[pco].ToString(); if (newTable.Columns.Contains(newName)) { newName += "_" + counter.ToString().Trim(); } newTable.Columns.Add(newName); counter += 1; } for (int col = 0; col <= oldTable.Columns.Count - 1; col++) { if (col == pco) { continue; } var dr = newTable.NewRow(); dr[0] = oldTable.Columns[col].ColumnName; for (var row = 0; row <= oldTable.Rows.Count - 1; row++) { if (StringExtensions.IsNullOrWhiteSpace(oldTable.Rows[row][col].ToString())) { dr[row + 1] = DBNull.Value; continue; } if (oldTable.Rows[row][col].ToString().Equals("System.Byte[]")) { dr[row + 1] = ByteArrayToString((byte[])oldTable.Rows[row][col]); } else if (oldTable.Columns[col].DataType.ToString().Equals("System.DateTime")) { dr[row + 1] = Convert.ToDateTime(oldTable.Rows[row][col]).ToString("yyyy-MM-dd HH:mm:ss.fff"); } else { dr[row + 1] = oldTable.Rows[row][col]; } } newTable.Rows.Add(dr); } return(newTable); }
/// <summary> /// Transposing the result with custom or generic header /// </summary> /// <param name="oldTable">The datatable object to transform</param> /// <param name="columnMapping">The custom header</param> /// <returns></returns> public static DataTable RotateTableWithKeyValue(DataTable oldTable, string columnMapping) { var newTable = new DataTable(); var dr = default(DataRow); // The column mapping is not provided,so create the generic header if (columnMapping == null) { //the first column name is key newTable.Columns.Add("Key"); var no = 0; foreach (DataRow row in oldTable.Rows) { newTable.Columns.Add("Value" + (no == 0 ? "" : no.ToString().Trim())); no++; } } else { var names = columnMapping.Split(','); foreach (var s in names) { newTable.Columns.Add(s); } // if we do not pass "enough" names, fill the rest with generic names if (names.Length < oldTable.Rows.Count + 1) { var no = names.Length; while (no < oldTable.Rows.Count + 1) { newTable.Columns.Add("Value" + no.ToString().Trim()); no++; } } } for (var col = 0; col <= oldTable.Columns.Count - 1; col++) { dr = newTable.NewRow(); dr[0] = oldTable.Columns[col].ColumnName; for (var row = 0; row <= oldTable.Rows.Count - 1; row++) { if (StringExtensions.IsNullOrWhiteSpace(oldTable.Rows[row][col].ToString())) { dr[row + 1] = DBNull.Value; continue; } if (oldTable.Rows[row][col].ToString().Equals("System.Byte[]")) { dr[row + 1] = ByteArrayToString((byte[])oldTable.Rows[row][col]); } else if (oldTable.Columns[col].DataType.ToString().Equals("System.DateTime")) { dr[row + 1] = Convert.ToDateTime(oldTable.Rows[row][col]).ToString("yyyy-MM-dd HH:mm:ss.fff"); } else { dr[row + 1] = oldTable.Rows[row][col]; } } newTable.Rows.Add(dr); } return(newTable); }