DataTable dt = Maticsoft.DBUtility.DbHelperOra.Query(QuerySql).Tables[0]; //DT的数据如下图所示
string CoverID = dt.Rows.Count > 0 ? dt.Rows[0]["CoverID"].ToString() : "";
#region 创建新的DataTable
DataTable dtShow = new DataTable();
dtShow.Columns.Add("序号", Type.GetType("System.String"));
dtShow.Columns.Add("部门", Type.GetType("System.String"));
dtShow.Columns.Add("编号", Type.GetType("System.String"));
dtShow.Columns.Add("姓名", Type.GetType("System.String"));
dtShow.Columns.Add("职务", Type.GetType("System.String"));
dtShow.Columns.Add("期间", Type.GetType("System.String"));
dtShow.Columns.Add("开始日期", Type.GetType("System.String"));
dtShow.Columns.Add("结束日期", Type.GetType("System.String"));
DataTable dtCover = Maticsoft.DBUtility.DbHelperOra.Query("select a.*,b.itemname from sa_accountci a left join sa_accountcoveritems b on a.itemid=b.itemid where a.coverid='" + CoverID + "' order by a.itemsortflag ").Tables[0];
if (dtCover.Rows.Count > 0)
{
for (int i = 0; i < dtCover.Rows.Count; i++)
{
dtShow.Columns.Add(dtCover.Rows[i]["ITEMNAME"].ToString(), Type.GetType("System.String"));
}
}
#endregion
#region 插入数据到新创建的DataTable
int p = 1; //行号
for (int n = 0; n < dt.Rows.Count; n++)
{
string EMPLOYEEID = dt.Rows[n]["EMPLOYEEID"].ToString();
//判断是否最末一行
string CompareEMPLOYEEID = (n == dt.Rows.Count - 1) ? "000000" : dt.Rows[n + 1]["EMPLOYEEID"].ToString();
if (CompareEMPLOYEEID != EMPLOYEEID) //比较当前行与下行ID 不同ID 则添加本行数据
{
DataRow dr = dtShow.NewRow();
dr["序号"] = p;
dr["部门"] = dt.Rows[n]["EMPLOYDEP"];
dr["编号"] = dt.Rows[n]["EMPLOYEEID"];
dr["姓名"] = dt.Rows[n]["EMPLOYEENAME"];
dr["职务"] = dt.Rows[n]["POST"];
dr["期间"] = dt.Rows[n]["CYCLENAME"];
dr["开始日期"] = dt.Rows[n]["CYCLESTARTDATE"];
dr["结束日期"] = dt.Rows[n]["CYCLEENDDATE"];
for (int j = 0; j < dtShow.Columns.Count; j++)
{
DataRow[] dr1 = dt.Select("EMPLOYEEID='" + EMPLOYEEID + "' AND ITEMNAME='" + dtShow.Columns[j].ColumnName + "'");
if (dr1.Length > 0)
{
dr[dtShow.Columns[j].ColumnName] = dr1[0]["AMOUNT"].ToString();
}
}
dtShow.Rows.Add(dr);
p++;
}
}
#endregion
处理后显示结果如下
