asked on

Transpose an ADO recordset

I've got a JET/ADO query in a VB6 app that returns a recordset with x1 to xn columns, y1 to yn rows, and a value (an average) for each cell. I need to transpose - in memory, not excel - this so that I have x1 to xn as rows, y1 to yn as column names and the correct value in each cell.

Anyone have any VB6 code that will do this? Note: the number of columns/rows is dynamic and changes depending on user inputs.

sort of like:

public function TranADOset(rstIn as adodb.recordsset) as adodb.recordset
'do the transpose
end function

SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thePrisoner

ASKER

I need to end up with an ADO recordset since I need to bid that to a grid. good point re getrows: got any code?

SOLUTION

stevbe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thePrisoner

ASKER

Stevbe,

How about Monday?

SOLUTION

stevbe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

nmilmine

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

stevbe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

thePrisoner

ASKER

Thanks stevbe, that was enough to get me there. FYI, here's the final code.

Private Function FlipTheGrid(rstIN As ADODB.Recordset, _
rstOUT As ADODB.Recordset, FLD0 As String) As Boolean
'transpose the recordset and return it
Dim i As Long
Dim x As Long
Dim fld As ADODB.field
'Debug.Print "RSTIN-FLDs: " & rstIN.Fields.Count '7
'Debug.Print "RSTIN-RECs: " & rstIN.RecordCount '14
i = 1
rstOUT.Fields.Append FLD0, adVarChar, 255 'the factor name fld
Do While Not rstIN.EOF
rstOUT.Fields.Append rstIN.Fields(0).Value, adVarChar, 255
i = i + 1
rstIN.MoveNext
Loop
i = 1
If rstIN.RecordCount > 0 Then
rstIN.MoveFirst
End If
rstOUT.Open
'Debug.Print "RSTOUT-FLDs: " & rstOUT.Fields.Count '14
'Debug.Print "RSTOUT-RECs: " & rstOUT.RecordCount '7
'pass 1: create a record for each factor, factor name as value for fld 1
i = 1
For x = 1 To rstIN.Fields.Count - 1
rstOUT.AddNew
rstOUT.Fields(0).Value = rstIN.Fields(x).name
rstOUT.UpdateBatch adAffectAllChapters
Next
'Debug.Print "RSTOUT-FLDs: " & rstOUT.Fields.Count '14
'Debug.Print "RSTOUT-RECs: " & rstOUT.RecordCount '7
rstOUT.MoveFirst
rstIN.MoveFirst
'pass 2 for each record in, write down record after record in that fld in OUT.
For i = 1 To rstOUT.Fields.Count - 1
rstOUT.MoveFirst
For x = 1 To rstIN.Fields.Count - 1
rstOUT.Fields(i).Value = Format(rstIN.Fields(x).Value, "#.00")
rstOUT.UpdateBatch adAffectAllChapters
rstOUT.MoveNext
Next
rstIN.MoveNext
Next
FlipTheGrid = True
End Function