-----------------------下面是我用的存储过程解决动态交叉表【测试没问题!】
create proc Picc_09
as
declare @sql varchar(8000)
set @sql='select 科室名称,'
select @sql=@sql+'sum(case when 险种='''+cast(险种 as varchar(26))+''' then 实收保费 else 0 end)
['+cast(险种 as varchar(26))+'],' from (select distinct top 100 percent 险种 from BD_DATA order by 险种)a set @sql =left(@sql,len(@sql)-1)+' from BD_DATA group by 科室名称 '
GO
-----------------------
请老师们,重点帮助我分析一下,如下问题:
1.执行PICC_09存储过程,要显示结果集的方法,对吗?
2.应该如何写?才能显示其结果集到网格控件中?
3.整体的思路对否?请专家明示?
-----------------------
VB6.0的代码如下:
Set RS1 = New ADODB.Recordset
Ssql = "picc_09"
Set RS1 = mConn.Execute(Ssql)
Q = 0
If Len(Ssql) > 0 Then
RS1.Open Ssql, mConn
--下一行求记录数不应等于0,应该有符合条件的结果记录7条。
If RS1.Fields.Count > 0 Then
Q = RS1.Fields.Count
clea_xy '刷新网格略!
call_grid '处理网格的标题及列宽
list_GRID RS1, MSFlexGrid2 '显示整个网格数据!
RS1.Close
End If
End If
Set RS1 = Nothing
----------------------
【以下2段过程调用,是我常用的控件和代码】
Sub call_grid() '处理字段名称_在网格中的列标题
On Error Resume Next
Dim L%
MSFlexGrid2.FixedCols = 1 '2
MSFlexGrid2.FixedRows = 1 '2
MSFlexGrid2.Cols = Q '- 1
MSFlexGrid2.Rows = 20
MSFlexGrid2.Row = 0
For L = 0 To Q ' '列数'MSFlexGrid2.Cols - 1
MSFlexGrid2.Col = L
MSFlexGrid2.Text = RS1.Fields(L).Name '字段名称
If RS1.Fields(L).DefinedSize < 40 Then '小于40的安1200执行,否,按2200执行。
MSFlexGrid2.ColWidth(L) = 1200 'Val(Fmt(i))
Else
MSFlexGrid2.ColWidth(L) = 2200
End If
Next
End Sub
---------
Sub list_GRID(ByRef RS As Recordset, ByRef MSFlexGrid2 As MSFlexGrid)'在执行sql语句后,得到的记录集逐条(含字段名)显示在Msflexgrid中
On Error Resume Next '/克服了Null or 0 的显示问题
Dim K, L As Integer
K = 0: L = 1
MSFlexGrid2.Row = 1
Do While Not RS1.EOF '显示符合条件的记录。
If MSFlexGrid2.Row >= MSFlexGrid2.Rows - 1 Then
MSFlexGrid2.Rows = MSFlexGrid2.Rows + 1
End If
For K = 0 To MSFlexGrid2.Cols - 1 'i - 1 '
MSFlexGrid2.Col = K:
If RS1.Fields(K) <> "" Then
MSFlexGrid2.Text = RS1.Fields(K)
Else
MSFlexGrid2.Text = ""
End If
Next K '列循环
MSFlexGrid2.Row = MSFlexGrid2.Row + 1
L = L + 1
RS1.MoveNext
Loop
MSFlexGrid2.ToolTipText = ""
MSFlexGrid2.ToolTipText = "表的记录数:" + CStr(L - 1)
End Sub
==========================
RS1.Open Ssql, mConn
--下一行求列数不应等于0,应该有符合条件的列有7条。
If RS1.Fields.Count > 0 Then
Q = RS1.Fields.Count
===================================
请专家或老师给予指导!!谢谢