c# - 如何将数据库表转换为 "pivot"DataTable？

Question

我有这样的数据，例如:

 | MemberID | Username | QuestionID |     Question     | Answer | 
 |----------|----------|------------|------------------|--------|
 |    58    |   John   |     1      | Recommend This?  |  Yes   |
 |    58    |   John   |     2      | How much rating? |  10    |
 |    59    |   Zess   |     1      | Recommend This?  |  No    |
 |    59    |   Zess   |     2      | How much rating? |  8     |

有一个这样的类(请忽略一些字段):

 class reportlist
    {
        public string MemberID { get; set; }
        public string Username { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public int QuestionID { get; set; }
        public string Question { get; set; }
        public string Answer { get; set; }
        public DateTime Submitted_On { get; set; }
        public int TotalPoint { get; set; }
    }

并有一个 mysql 阅读器将数据添加到报告列表中:

 while (reader.Read())
 {
       reportlist.Add(new reportlist() { MemberID = reader["MemberID"].ToString(), Username = reader["Username"].ToString(),
                        Name = reader["Name"].ToString(), Email = reader["Email"].ToString(), QuestionID = int.Parse(reader["QuestionID"].ToString()), Question = reader["Question"].ToString(),
                        Answer = reader["Answer"].ToString(), Submitted_On = DateTime.Parse(reader["CreationDate"].ToString()), TotalPoint = int.Parse(reader["TotalPoint"].ToString()) });                        
 }

现在是我卡住的地方，我尝试使用 Linq 查询:

var groupedList = reportlist
           .GroupBy(u => u.QuestionID, u => u.MemberID)
           .ToList();

DataTable dt = new DataTable();

foreach (var group in groupedList)
{
   //add columns, rows to datatable, stuck here
}

并且不确定如何将这些结果获取到数据表中。基本上，我想将问题分组并让它成为列，并将所有数据添加到数据表以形成这些结果(示例):

 | MemberID | Username | Recommend This? | How much rating? | 
 | 58       |   John   |      Yes        |       10         | 
 | 59       |   Zess   |      No         |        8         | 

请指导如何获得结果或更好的解决方案？我被困了很长一段时间，因为我不擅长 linq。我确实尝试四处搜索，结果不多，可能不太明白它是如何工作的。

补充信息:最终数据表用于 EPPlus 库(生成 excel 电子表格)

Answer 1

显然，您在问题中发布的代码不可能是您的真实代码，因为它具有以下特点，永远不会编译:

 class reportlist { ... }
 reportlist.Add(new reportlist() { ... });

做出一些假设以便能够回答问题:

List<reportlist> list; // the list filled by the data reader.

您可以通过几个步骤制作“数据透视表”(即将行值转换为列):

// Get unique question info.
var questions = list
    .GroupBy(x => x.QuestionID)
    .Select(x => new { Id = x.Key, Title = x.First().Question });

// Set up the columns for the data table as a "pivot table" with columns per question.
DataTable myTable = new DataTable("QuestionsPivotTable");
var fixedColumns = new [] { 
    new DataColumn("Member ID", typeof(string)),
    new DataColumn("User Name", typeof(string)),
};
var questionColumns = questions.Select(x => new DataColumn(x.Title, typeof(string)));
myTable.Columns.AddRange(fixedColumns.Concat(questionColumns).ToArray());

通过包含“透视”问题列的数据表设置，我们可以为每个成员获取一行作为值数组:

// Get the column values for each member as an array.
var rows = list
    .GroupBy(x => x.MemberID)
    .Select(x => {
        var memberCols = new object[] { x.Key, x.First().Username };
        var answerCols = questions.Select(q => x.Where(a => a.QuestionID == q.Id).Select(a => a.Answer).FirstOrDefault());
        return memberCols.Concat(answerCols).ToArray();
    });

现在很容易填充表格:

// Fill the table.
foreach (var memberRow in rows)
{
    var row = myTable.NewRow().ItemArray = memberRow;
    myTable.Rows.Add(row);
}

其中，使用您的示例数据，以下代码:

// Show the output
for (var i = 0; i < myTable.Columns.Count - 1; i++)
    Console.Write("{0}, ", myTable.Columns[i]);
Console.WriteLine("{0}", myTable.Columns[myTable.Columns.Count - 1]);
foreach (var row in myTable.AsEnumerable())
    Console.WriteLine(string.Join(", ", row.ItemArray.Select(x => x == null ? "<null>" : x.ToString())));

产生这个输出:

Member ID, User Name, Recommend This?, How much rating?
58, John, Yes, 10
59, Zess, No, 8