主题：关于垂直表和水平表的转换

我想实现从11那样的表转换成22那样的表，在转换过程中，如何把11表中的日期转换成22中的分月份呢
此主题相关图片如下：22.jpg

没理解你什么意思

 

Dim dtb As New DataTableBuilder("损益进度(总体)")
dtb.AddDef("所属单位", Gettype(String), 8)
dtb.AddDef("一级科目", Gettype(String), 10)
dtb.AddDef("利润_一月", Gettype(Double))
dtb.AddDef("利润_二月", Gettype(Double))
dtb.Build()

http://www.foxtable.com/bbs/dispbbs.asp?BoardID=2&ID=68546&skin=0

 

好，先研究一下

研究了一下，感觉简单的还能弄得明白，自己这表相对复杂了点，转换前后的统计列都是多列的，而且要求转换后的数据具有汇总性，只能求助了。

Dim dtb As New DataTableBuilder("统计表", "损益进度(总体)")
dtb.AddDef("所属单位", Gettype(String), 8)
dtb.AddDef("一级科目", Gettype(String), 10)
Dim dt As DataTable = DataTables("利润表附表")
Dim mind As Date = dt.compute("min(日期)")
Dim maxd As Date = dt.compute("max(日期)")
mind = new Date(mind.year, mind.Month, 1)
maxd = new Date(maxd.year, maxd.Month, 1)
Do While mind <= maxd
    dtb.AddDef(Format(mind, "j_yyyy-MM-01"), Gettype(Double), "",Format(mind, "yyyy年MM月"))
    mind = mind.AddMonths(1)
Loop
dtb.AddDef("合计", Gettype(Double))
dtb.Build()
Dim t As Table = Tables("统计表")
Dim kms() As String = {"利润_本月","主营业务收入_本月"}
Dim kmsz() As String = {"利润","主营业务收入"}
For Each ary As String In dt.GetValues("所属单位")
    For i As Integer = 0 To kms.length-1
        Dim km As String = kms(i)
        Dim kmz As String = kmsz(i)
        Dim nr As Row = t.addnew
        nr("所属单位") = ary
        nr("一级科目") = kmz
        Dim sum As Double = 0
        For Each c As Col In t.Cols
            If c.name Like "j_*" Then
                Dim d As Date = c.name.split("_")(1)
                nr(c.name) = dt.compute("sum(" & km & ")", "所属单位 = '" & ary & "' and 日期>=#" & d & "# and 日期<#" & d.AddMonths(1) & "#")
                sum += nr(c.name)
            End If
        Next
        nr("合计") = sum
    Next
Next
MainTable = t

好复杂啊，研究研究，谢谢

如我上传的项目中，如果我的“损益进度"再增加一列”部门“，我又弄不来了，而且我希望生成的”损益进度“能够按照”科目“汇总，好难啊。

Dim dtb As New DataTableBuilder("统计表", "损益进度(总体)")
dtb.AddDef("所属单位", Gettype(String), 8)
dtb.AddDef("部门", Gettype(String), 20)
dtb.AddDef("一级科目", Gettype(String), 10)
Dim dt As DataTable = DataTables("利润表附表")
Dim mind As Date = dt.compute("min(日期)")
Dim maxd As Date = dt.compute("max(日期)")
mind = new Date(mind.year, mind.Month, 1)
maxd = new Date(maxd.year, maxd.Month, 1)
Do While mind <= maxd
    dtb.AddDef(Format(mind, "j_yyyy-MM-01"), Gettype(Double), "",Format(mind, "yyyy年MM月"))
    mind = mind.AddMonths(1)
Loop
dtb.AddDef("合计", Gettype(Double))
dtb.Build()
Dim t As Table = Tables("统计表")
Dim kms() As String = {"利润_本月","主营业务收入_本月"}
Dim kmsz() As String = {"利润","主营业务收入"}
For Each ary() As String In dt.GetValues("所属单位|部门")
    For i As Integer = 0 To kms.length-1
        Dim km As String = kms(i)
        Dim kmz As String = kmsz(i)
        Dim nr As Row = t.addnew
        nr("所属单位") = ary(0)
        nr("部门") = ary(1)
        nr("一级科目") = kmz
        Dim sum As Double = 0
        For Each c As Col In t.Cols
            If c.name Like "j_*" Then
                Dim d As Date = c.name.split("_")(1)
                nr(c.name) = dt.compute("sum(" & km & ")", "所属单位 = '" & ary(0) & "' and 部门 = '" & ary(1) & "' and 日期>=#" & d & "# and 日期<#" & d.AddMonths(1) & "#")
                sum += nr(c.name)
            End If
        Next
        nr("合计") = sum
    Next
Next
MainTable = t

有点明白了，谢谢