是否可以在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"},{"是"}];

提前感谢:)

是否可以在C#.net中将行转换为列并列出数据

试试这个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();