将Datatable行的数据格式化为Datacolumn。 [英] Format Datatable rows to Datacolumn with respect to ID.

问题描述

我有以下格式的数据表。



ID Intalment Amount Date



1 FirstInst 10000 12 \ 04\ 2014



1 SecondInst 10000 13\04\ 2014



1 ThirdInst 5000 14\04\ 2014



2 FirstInst 10000 14\04\ 2014



2 SecondInst 7000 17 \\\ 2014 2014





我想要这样的东西......



ID FirstInst SecondInst ThirdInst FirstInstDate SecondInstDate ThirdInstDate



1 10000 10000 5000 12\04\ 201414 13\04\ 2014 14 \ 04\ 2014



2 10000 7000 null 14 \\\ 201414 17 \\\\\ 2014 null null

I have datatable in following format.

ID Intalment Amount Date

1 FirstInst 10000 12\04\2014

1 SecondInst 10000 13\04\2014

1 ThirdInst 5000 14\04\2014

2 FirstInst 10000 14\04\2014

2 SecondInst 7000 17\04\2014


I want something like this...

ID FirstInst SecondInst ThirdInst FirstInstDate SecondInstDate ThirdInstDate

1 10000 10000 5000 12\04\2014 13\04\2014 14\04\2014

2 10000 7000 null 14\04\2014 17\04\2014 null

推荐答案

试试这个;

Try this;

var dtTable1 = new DataTable("Table1");
           var dtTable2 = new DataTable("Table2");


           dtTable1.Columns.AddRange
               (
                   new DataColumn[]
                       {
                           new DataColumn("ID", typeof (int)),
                           new DataColumn("Intalment", typeof (string)),
                           new DataColumn("Amount", typeof (float)),
                           new DataColumn("Date", typeof (DateTime))
                       }
               );


           dtTable2.Columns.AddRange
               (
                   new DataColumn[]
                   {
                       new DataColumn("ID", typeof (int)),
                       new DataColumn("FirstInstAmount", typeof (float)),
                       new DataColumn("SecondInstAmount", typeof (float)),
                       new DataColumn("ThirdInstAmount", typeof (float)),
                       new DataColumn("FirstInstDate", typeof (DateTime)),
                       new DataColumn("SecondInstDate", typeof (DateTime)),
                       new DataColumn("ThirdInstDate", typeof (DateTime))
                   }
               );

           var rowCon = dtTable1.NewRow();
           rowCon["ID"] = 1;
           rowCon["Intalment"] = "FirstInst";
           rowCon["Amount"] = 10000;
           rowCon["Date"] = new DateTime(2014, 4, 12);
           dtTable1.Rows.Add(rowCon);

           rowCon = dtTable1.NewRow();
           rowCon["ID"] = 1;
           rowCon["Intalment"] = "SecondInst";
           rowCon["Amount"] = 10000;
           rowCon["Date"] = new DateTime(2014, 4, 13);
           dtTable1.Rows.Add(rowCon);

           rowCon = dtTable1.NewRow();
           rowCon["ID"] = 1;
           rowCon["Intalment"] = "ThirdInst";
           rowCon["Amount"] = 5000;
           rowCon["Date"] = new DateTime(2014, 4, 14);
           dtTable1.Rows.Add(rowCon);

           rowCon = dtTable1.NewRow();
           rowCon["ID"] = 2;
           rowCon["Intalment"] = "FirstInst";
           rowCon["Amount"] = 10000;
           rowCon["Date"] = new DateTime(2014, 4, 14);
           dtTable1.Rows.Add(rowCon);

           rowCon = dtTable1.NewRow();
           rowCon["ID"] = 2;
           rowCon["Intalment"] = "SecondInst";
           rowCon["Amount"] = 7000;
           rowCon["Date"] = new DateTime(2014, 4, 17);
           dtTable1.Rows.Add(rowCon);


           var aa = from r in dtTable1.AsEnumerable()
                    group r by r.Field<int>("ID") into g
                    select new
                    {
                        g.Key,
                        firstIntalAmount = g.Where(s => s.Field<string>("Intalment").Equals("FirstInst")).Select(t => t.Field<float>("Amount")).FirstOrDefault(),
                        secondIntalAmount = g.Where(s => s.Field<string>("Intalment").Equals("SecondInst")).Select(t => t.Field<float>("Amount")).FirstOrDefault(),
                        thirdIntalAmount = g.Where(s => s.Field<string>("Intalment").Equals("ThirdInst")).Select(t => t.Field<float>("Amount")).FirstOrDefault(),
                        firstIntalDate = g.Where(s => s.Field<string>("Intalment").Equals("FirstInst")).Select(t => t.Field<DateTime>("Date")).FirstOrDefault(),
                        secondIntalDate = g.Where(s => s.Field<string>("Intalment").Equals("SecondInst")).Select(t => t.Field<DateTime>("Date")).FirstOrDefault(),
                        thirdIntalDate = g.Where(s => s.Field<string>("Intalment").Equals("ThirdInst")).Select(t => t.Field<DateTime>("Date")).FirstOrDefault()
                    };

           foreach (var item in aa)
           {
               var row = dtTable2.NewRow();
               row["ID"] = item.Key;
               row["FirstInstAmount"] = item.firstIntalAmount;
               row["SecondInstAmount"] = item.secondIntalAmount;
               row["ThirdInstAmount"] = item.thirdIntalAmount;
               row["FirstInstDate"] = item.firstIntalDate;
               row["SecondInstDate"] = item.secondIntalDate;
               row["ThirdInstDate"] = item.thirdIntalDate;
               dtTable2.Rows.Add(row);
           }

这篇关于将Datatable行的数据格式化为Datacolumn。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！