将数据表结果转置到另一个数据表 [英] Transpose data table result to another data table
本文介绍了将数据表结果转置到另一个数据表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
想在vb.net上询问你如何做到这一点的帮助..
极品将数据表结果转置到另一个数据表。
特定ID#的所有日志应该只重新排列成一行。
这就是场景。
数据表1结果
ID#日志日期/时间
111111 09/10/2018 7:30
111111 09/10/2018 10:30
111111 09/10/2018 13:30
111111 09/10/2018 17:30
222222 09/10/2018 7:30
222222 09/10/2018 17:30
333333 09/10/2018 7:30
333333 09/10/2018 18:30
将数据表1转置到此
数据表2
ID#日志日期/时间1日志日期/时间2日志日期/时间3日志日期/时间4
111111 09/10/2018 7:30 09/10/2018 10:30 09/10 / 2018 13:30 09/10/2018 17:30
222222 09/10/2018 7:30 09/10/2018 17:30
333333 09/10/2018 7:30 09/10 / 2018 18:30
我尝试了什么:
无法在网上找到这种情况或场景,这就是为什么决定发布它这里。希望有关如何实现所需结果的想法。
提前谢谢。
解决方案
这是相当的简单,想一想!
至少有两种方法可以达到这个目的。
1.非Linq解决方案:
1.1。您需要做的就是通过ID#
字段然后按<$对DataTable(dt
)中的数据进行排序c $ c>记录日期/时间字段。
1.2使用<创建新的DataTable对象(假设dtFinal
) code> dt.Clone()方法并删除记录日期/时间
字段。
1.3循环通过dt
对象行的集合。
1.3.1然后在循环中:
- 直到ID#
与以前相同
- 检查DataTable中是否已存在日志日期/时间{No}
(如果没有 - 添加它)
- 将日期从日期日期/时间
字段dt
对象复制到日期日期/时间{否}
字段dtFinal
对象
2. Linq解决方案(阅读以下代码块中的注释):
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn( )_
{_
新DataColumn(ID#,Type.GetType(System.Int32)),_
新DataColumn(日期日期/时间,Type.GetType (System.DateTime))_
})
dt.Rows.Add(New Object(){111111,#09/10/2018 7:30#})
dt.Rows.Add(New Object(){111111,#09/10/2018 10:30#})
dt.Rows.Add(New Object(){111111,#09/10/2018 13:30#})
dt.Rows.Add(New Object(){111111,#09/10/2018 17:30#})
dt.Rows.Add(New Object(){ 222222,#09/10/2018 7:30#})
dt.Rows.Add(New Object(){222222,#09/10/2018 17:30#})
dt.Rows .Add(New Object(){333333,#09/10/2018 7:30#})
dt.Rows.Add(New Object(){333333,#09/10/2018 18:30#} )
'克隆DataTa ble
Dim dtFinal As DataTable = dt.Clone()
'删除第二列=> 记录日期/时间
dtFinal.Columns.Remove(记录日期/时间)
'按ID分组数据#
Dim dataGroupedByID = dt.AsEnumerable()_
.OrderBy(Function(x)x.Field(Of Integer)(ID#))_
.ThenBy(Function(x)x.Field(Of DateTime)(记录日期/时间) ))_
.GroupBy(Function(x)x.Field(Of Integer)(ID#))_
.Select(Function(grp)New With _
{_
.ID = grp.Key,_
。日期= grp.Select(函数(x)x.Field(Of DateTime)(日期日期/时间))。ToList()_
})_
.ToList()
'获取记录日期/时间的最大数量
'能够找出od列数记录日期/时间必须添加
Dim maxOccurance = dataGroupedByID _
.Select(Function(x)x.Dates.Count())_
.Max()
'添加日志日期/时间列
Dim i As Integer = 0
For i = 1 to maxOccurance
dtFinal.Columns.Add(New DataColumn(String.Format( 记录日期/时间{0},i),Type.GetType(System.DateTime)))
下一个
'lo op throug dataGroupedByID列表中的项目集合
For each item in dataGroupedByID
'定义新行
Dim dr = dtFinal.NewRow()
'
dr( ID#)= item.ID
'将日期插入相应的列
For i = 0 To item.Dates.Count() - 1
dr(String.Format(Log date /时间{0},i + 1))= item.Dates(i)
下一个
'添加行
dtFinal.Rows.Add(dr)
下一步
Hi Everyone,
Just would like to ask for your help on how to do this on vb.net..
Need to transpose data table result to another data table.
All logs of a particular ID# should be re-arrange into one row only.
This is the scenario.
Data table 1 result
ID# Log date/time 111111 09/10/2018 7:30 111111 09/10/2018 10:30 111111 09/10/2018 13:30 111111 09/10/2018 17:30 222222 09/10/2018 7:30 222222 09/10/2018 17:30 333333 09/10/2018 7:30 333333 09/10/2018 18:30
Transpose data table 1 to this
Data table 2
ID# Log date/time 1 Log date/time 2 Log date/time 3 Log date/time 4 111111 09/10/2018 7:30 09/10/2018 10:30 09/10/2018 13:30 09/10/2018 17:30 222222 09/10/2018 7:30 09/10/2018 17:30 333333 09/10/2018 7:30 09/10/2018 18:30
What I have tried:
Cannot found such kind of case or scenario on the web that is why decided to post it here. Hoping for an idea on how to achieve needed result.
Thanks in advance.
解决方案
It's quite simple, think of it!
There's at least two ways to achieve that.
1. non-Linq solution:
1.1. All you need to do is to sort data in a DataTable (dt
) byID#
field and then byLog date/time
field.
1.2 Create new DataTable object (let's saydtFinal
) usingdt.Clone()
method and removeLog date/time
field.
1.3 Loop through the collection of rows ofdt
object.
1.3.1 Then inside a loop:
- untilID#
is the same as previous
- check ifLog date/time{No}
already exists in a DataTable (if not - add it)
- copy date fromLog date/time
field ofdt
object intoLog date/time{No}
field ofdtFinal
object
2. Linq solution (read comments in below code block):
Dim dt As DataTable = New DataTable() dt.Columns.AddRange(New DataColumn() _ { _ New DataColumn("ID#", Type.GetType("System.Int32")), _ New DataColumn("Log date/time", Type.GetType("System.DateTime")) _ }) dt.Rows.Add(New Object(){111111, #09/10/2018 7:30#}) dt.Rows.Add(New Object(){111111, #09/10/2018 10:30#}) dt.Rows.Add(New Object(){111111, #09/10/2018 13:30#}) dt.Rows.Add(New Object(){111111, #09/10/2018 17:30#}) dt.Rows.Add(New Object(){222222, #09/10/2018 7:30#}) dt.Rows.Add(New Object(){222222, #09/10/2018 17:30#}) dt.Rows.Add(New Object(){333333, #09/10/2018 7:30#}) dt.Rows.Add(New Object(){333333, #09/10/2018 18:30#}) 'clone DataTable Dim dtFinal As DataTable = dt.Clone() 'remove second column => "Log date/time" dtFinal.Columns.Remove("Log date/time") 'group data by ID# Dim dataGroupedByID = dt.AsEnumerable() _ .OrderBy(Function(x) x.Field(Of Integer)("ID#")) _ .ThenBy(Function(x) x.Field(Of DateTime)("Log date/time")) _ .GroupBy(Function(x) x.Field(Of Integer)("ID#")) _ .Select(Function(grp) New With _ { _ .ID = grp.Key, _ .Dates = grp.Select(Function(x) x.Field(Of DateTime)("Log date/time")).ToList() _ }) _ .ToList() 'get max count of occurences of "Log date/time" 'to be able to find out how many columns od "Log date/time" have to be added Dim maxOccurance = dataGroupedByID _ .Select(Function(x) x.Dates.Count()) _ .Max() 'add "Log date/time" columns Dim i As Integer = 0 For i = 1 To maxOccurance dtFinal.Columns.Add(New DataColumn(String.Format("Log date/time {0}", i), Type.GetType("System.DateTime"))) Next 'loop throug the collection of items in dataGroupedByID list For Each item In dataGroupedByID 'define new row Dim dr = dtFinal.NewRow() ' dr("ID#") = item.ID 'insert date into corresponding column For i = 0 To item.Dates.Count()-1 dr(String.Format("Log date/time {0}", i+1)) = item.Dates(i) Next 'add row dtFinal.Rows.Add(dr) Next
这篇关于将数据表结果转置到另一个数据表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文