是否可以在C#.net中将行转换为列并列出数据
本文关键字:转换 数据 net 是否 | 更新日期: 2023-09-27 18:24:35
我有两个不同项目的列表,如Employees和Departments,它们都有多对多关系。
员工:
EmpID EmpName1 emp12 emp23 emp3
部门:
DeptID DeptName1个部门12深度23深度3
关系表relationID员工ID部门ID1 1 12 1 23 2 34 3 15 3 3
和我的要求:和部门不固定
员工ID姓名部门1部门2部门31 emp1是是否2 emp2否是3 emp3是不是是
可以这样做吗?如果可以,请点燃我,谢谢。
Json数据将是,emplist:[{"empID":"1","name":"emp1","Dept1":"yes","dept2":"yes","Depat3":"yes"},{"是"}];
提前感谢:)
试试这个linq
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication58
{
class Program
{
static void Main(string[] args)
{
DataTable employeesTable = new DataTable();
employeesTable.Columns.Add("EmpID", typeof(int));
employeesTable.Columns.Add("EmpName", typeof(string));
employeesTable.Rows.Add(new object[] {1, "emp1"});
employeesTable.Rows.Add(new object[] {2, "emp2"});
employeesTable.Rows.Add(new object[] {3, "emp3"});
DataTable departmentTable = new DataTable();
departmentTable.Columns.Add("DeptID", typeof(int));
departmentTable.Columns.Add("DeptName", typeof(string));
departmentTable.Rows.Add(new object[] {1, "dept1"});
departmentTable.Rows.Add(new object[] {2, "dept2"});
departmentTable.Rows.Add(new object[] {3, "dept3"});
DataTable relationTable = new DataTable();
relationTable.Columns.Add("RelationID", typeof(int));
relationTable.Columns.Add("EmpID", typeof(int));
relationTable.Columns.Add("DeptID", typeof(int));
relationTable.Rows.Add(new object[] {1, 1, 1});
relationTable.Rows.Add(new object[] {2, 1, 2});
relationTable.Rows.Add(new object[] {3, 2, 3});
relationTable.Rows.Add(new object[] {4, 3, 1});
relationTable.Rows.Add(new object[] {5, 3, 3});
var joinTables =
(from r in relationTable.AsEnumerable()
join e in employeesTable.AsEnumerable() on r.Field<int>("EmpID") equals e.Field<int>("EmpID")
join d in departmentTable.AsEnumerable() on r.Field<int>("DeptID") equals d.Field<int>("DeptID")
select new {
relationID = r.Field<int>("RelationID"),
employeeID = e.Field<int>("EmpID"),
employeeName = e.Field<string>("EmpName"),
department = d.Field<string>("DeptName")
})
.ToList();
var results = joinTables.GroupBy(x => x.employeeID).Select(y => new {
employee = new {
empID = y.FirstOrDefault().employeeID,
empName = y.FirstOrDefault().employeeName,
Dept1 = y.Where(z => z.department == "dept1").Any() ? "yes" : "no",
Dept2 = y.Where(z => z.department == "dept2").Any() ? "yes" : "no",
Dept3 = y.Where(z => z.department == "dept3").Any() ? "yes" : "no"
}
}).ToList();
}
}
}
如果你有一个大的部门列表,你可以这样做,而不是
List<string> departments = departmentTable.AsEnumerable().Select(x => x.Field<string>("DeptName")).Distinct().OrderBy(y => y).ToList();
var results = joinTables.GroupBy(x => x.employeeID).Select(y => new {
employee = new {
empID = y.FirstOrDefault().employeeID,
empName = y.FirstOrDefault().employeeName,
departments = departments.Select(d => y.Where(z => z.department == d).Any() ? d + ": yes" : d + ": no").ToList()
}
}).ToList();