在C#中进行数据纵向不定行转横向列,多条信息成一行,例如员工薪资信息

2016/12/29 15:39
阅读数 30
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


处理后显示结果如下

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部