Hi There
I have a nasty piece of SQL which queries 4 tables using Left & right joins, this is because each record may have many different categories associated with it and so a record is returned for each different category found ie : -
recid1 somemoredata category a
recid1 somemoredata category b
recid1 somemoredata category c
recid2 record2data category 1
recid2 record2data category 2

As you can see the net result of which is a recordset containing a lot of redundant data. Now I needed this reformatted similarly to output from the ado getrows method, ie Columns/Rows so that the categories were appended onto the end of one record so that the above example would be rendered as

recid1 somemoredata category acategory bcategory c
recid2 record2data category 1category 2

I did this but think my method was pretty inefficent, I did mine by using the dictionary object and a new array. If anyone is remotely interested I can post the code. I also worked out another way by just transferring data directly from the original array to a new 2-way array.

I also thought data shaping might cut down on the redundant data aspect, anyway all other thoughts are welcome.

Decided to rewrite it using a combination of data-shaping and arrays
which gives me code similar to this :-

BTW if you're looking for response.write commands, this is the VB6 version. This seems to be an excellent way of removing all those extraneous lines cused by left & right joins.

Dim objConn As ADODB.Connection
Dim strConnString As String
Dim strSql As String
Dim rsMain As ADODB.Recordset
Dim rsInter As ADODB.Recordset
Dim rsCats As ADODB.Recordset
Dim arrData() As String
Dim arrIndx As Integer
Dim strCatType As String

  Set objConn = New ADODB.Connection
   objConn.Provider = "MSDataShape"

   strConnString = "Provider=MSDataShape;" & _
           "Data Provider=SQLOLEDB;" & _
           "Data Source=<yourServer>;" & _
           "Initial Catalog=<your database>;" & _
           "User Id=<uid>;" & _
           "Password=<yourpassword>"

   objConn.ConnectionString = strConnString
   objConn.Open

   strSql = _
    "SHAPE { SELECT Vacancy.*, Location.Location FROM Vacancy, Location where Vacancy.LocId = Location.LocId ORDER BY VacDesc }" & _
    " Append ((" & _
    "SHAPE { SELECT * FROM VacCat ORDER BY VacId} as RSInter" & _
    " Append (" & _
    "{SELECT * FROM Categories ORDER BY CatDesc} AS RSCats" & _
    " RELATE CatId TO CatId))" & _
    " RELATE VacId TO VacId)"

   Set rsMain = New ADODB.Recordset
   rsMain.Open strSql, objConn
   'Our class recordset

   arrIndx = 0
   Do While Not rsMain.EOF
   If arrIndx = 0 Then
      ReDim arrData(arrIndx)
   Else
      ReDim Preserve arrData(arrIndx)
   End If
   arrData(arrIndx) = rsMain("VacId").Value & " " & rsMain("VacDesc").Value & " " & rsMain("Location").Value
    Set rsInter = rsMain("RSinter").Value
    Do While Not rsInter.EOF
        Set rsCats = rsInter("RSCats").Value
        Do While Not rsCats.EOF
            ' Processing here
            If rsCats("CatType").Value = 1 Then
               strCatType = " Client - "
            Else
               strCatType = " Activity - "
            End If

            arrData(arrIndx) = arrData(arrIndx) & strCatType & rsCats("CatDesc").Value & "/"
            rsCats.MoveNext
        Loop
        rsInter.MoveNext
     Loop
     arrIndx = arrIndx + 1
     rsMain.MoveNext
   Loop

   For arrIndx = 0 To UBound(arrData)
       Debug.Print arrData(arrIndx)
   Next

   'Clean up...
   rsMain.Close
   Set rsMain = Nothing

   'Clean up...
   rsInter.Close
   Set rsInter = Nothing

   'Clean up...
   rsCats.Close
   Set rsCats = Nothing

   objConn.Close
   Set objConn = Nothing

Reagrding you First post, I am not sure about the column names there. But something like this should work for you. You can take some ideas from this thread

http://p2p.wrox.com/topic.asp?TOPIC_ID=12476

OR

http://msdn.microsoft.com/library/de...qd_14_04j7.asp

Hope that helps.
Cheers!

-Vijay G