主题：狐表不支持纵向表转横向表？（分享LXL版主SQL纵转横代码）

sql 俺不会搞！ 帮你顶～～～～～～～～

但用狐表又[最好不用狐表的办法。。。 ] 说不通啊！～   当然，哪种方法更简便就用之呗～ 何必难为自己？  
如有sql论坛，最好到那里去问一问。   

中午睡了一觉， 困死了～     这段代码应该也可以的： 你表C的后两行、后三列的数据不对吧？

dim b as string =DataTables("表B").GetComboListString("编码")
dim a as string = DataTables("表A").GetComboListString("费别","","序号")
Dim dtb As New DataTableBuilder("统计")
dtb.AddDef("编码", GetType(string), 12)
for i as integer = 0 to a.split("|").length -1
    dtb.AddDef(a.split("|")(i), GetType(double), 32)
next
dtb.Build()
Maintable = Tables("统计")
for ii as integer = 0 to b.split("|").length-1
    Dim r As Row = Tables("统计").Rows.AddNew()
    Tables("统计")(ii,"编码") = b.split("|")(ii)
next
for r as integer = 0 to Tables("统计").Count - 1
    For c As integer = 1 To Tables("统计").Cols.Count - 1
        Dim dc As DataRow
        dc = DataTables("表A").Find("费别 = '" & Tables("统计").Cols(c).name & "'")
        Dim dr As DataRow
        dr = DataTables("表B").Find( "编码 = '" & r+1 & "' and 费别序号 = '" & dc("序号") & "'")
        if dr isnot nothing
            currenttable(r,c)= dr("金额")
        else
            currenttable(r,c)= 0      '没有的列为0
        end if
    next
next

简化一下：：： 就是把上面两个for 。。。 next 合并起来。

 

dim b as string =DataTables("表B").GetComboListString("编码")

dim a as string = DataTables("表A").GetComboListString("费别","","序号")

Dim dtb As New DataTableBuilder("统计")

dtb.AddDef("编码", GetType(string), 12)

for i as integer = 0 to a.split("|").length -1

    dtb.AddDef(a.split("|")(i), GetType(double), 32)

next

dtb.Build()

Maintable = Tables("统计")

for ii as integer = 0 to b.split("|").length-1

    Dim r As Row = Tables("统计").Rows.AddNew()

    Tables("统计")(ii,"编码") = b.split("|")(ii)

    For c As integer = 1 To Tables("统计").Cols.Count - 1

        Dim dc As DataRow

        dc = DataTables("表A").Find("费别 = '" & Tables("统计").Cols(c).name & "'")

        Dim dr As DataRow

        dr = DataTables("表B").Find( "编码 = '" & ii+1 & "' and 费别序号 = '" & dc("序号") & "'")

        if dr isnot nothing

            Tables("统计")(ii,c)= dr("金额")

        else

            Tables("统计")(ii,c)= 0      '没有的列为0

        end if

    next

next

思路不一定好，但能达到要求：

 

在表B中增加: 金额、自费金额、统筹金额 三列（可以是临时列） : 这样可以不要表C了！ 

for tzj as integer = 0 to tables("表B").count -1

    tables("表B")(tzj,"金额") = tables("表B")(tzj,"单价")*tables("表B")(tzj,"数量")

    tables("表B")(tzj,"自费金额") = tables("表B")(tzj,"单价")*tables("表B")(tzj,"数量")*tables("表B")(tzj,"参数")

    tables("表B")(tzj,"统筹金额") = tables("表B")(tzj,"单价")*tables("表B")(tzj,"数量")-tables("表B")(tzj,"自费金额")

next

【如果一定要表C，下面代码稍加修改也可以的】
 

表A窗口按钮：

dim b as string =DataTables("表B").GetComboListString("编码")

dim a as string = DataTables("表A").GetComboListString("费别","","序号")

Output.Show(a)

Dim dtb As New DataTableBuilder("统计")

dtb.AddDef("编码", GetType(string), 12)

for i as integer = 0 to a.split("|").length -1

    dtb.AddDef(a.split("|")(i), GetType(double), 32)

next

dtb.AddDef("合计", GetType(double), 32)                 '新增

dtb.AddDef("报销金额", GetType(double), 32)             '新增

dtb.AddDef("自付金额", GetType(double), 32)             '新增

dtb.Build()

Maintable = Tables("统计")

for ii as integer = 0 to b.split("|").length-1

    Dim r As Row = Tables("统计").Rows.AddNew()

    Tables("统计")(ii,"编码") = b.split("|")(ii)

    For c As integer = 1 To Tables("统计").Cols.Count-4

        Dim dc As DataRow

        dc = DataTables("表A").Find("费别 = '" & Tables("统计").Cols(c).name & "'")

        Dim dr As DataRow

        dr = DataTables("表B").Find( "编码 = '" & ii+1 & "' and 费别序号 = '" & dc("序号") & "'")

        if dr isnot nothing

            Tables("统计")(ii,c)= dr("金额")

        else

            Tables("统计")(ii,c)= 0      '没有的列为0

        end if

        Tables("统计")(ii,"合计") = Tables("统计")(ii,"合计") + Tables("统计")(ii,Tables("统计").Cols(c).name)

        Tables("统计")(ii,"报销金额")= 100   '**假定： (dr("统筹金额") - 起付线(不确定数))*报销比例(不确定数) = 100

        Tables("统计")(ii,"自付金额")= Tables("统计")(ii,"合计")-Tables("统计")(ii,"报销金额")

    next

next

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:狐表不支持纵向表转横向表？sql纵转横 管理项目5.rar