UNKNOWN '************************************** ' Name: A ADO Data Shaping or Multiple S ' QL Select ' Description:Do you have slow running m ' ulitple SELECT Statements or long report ' s to fill on a web page. Use the Microso ' ft Shape Command. Learn to use ADO 2.1 a ' nd greater advance features. This code i ' s great for three things, (1) Very fast ' way to do multiple SQL select statements ' and reports. (2) Great for databases not ' Normalized. (3) Avoids multiple nested s ' ingle threaded ADO Record Sets loops whi ' ch are very slow. ' By: Rob Gerwing ' ' ' Inputs:None ' ' Returns:None ' 'Assumes:Basic ADO Recordset use in Micr ' osoft Active server Pages. Basic Knowled ' ge of SQL "SELECT" statements. ' 'Side Effects:None 'This code is copyrighted and has limite ' d warranties. 'Please see http://www.Planet-Source-Cod ' e.com/xq/ASP/txtCodeId.6512/lngWId.4/qx/ ' vb/scripts/ShowCode.htm 'for details. '************************************** &lt;%Response.Buffer = true Const adOpenForwardOnly = 2 Dim connShape,strShape Dim objConn,objRS,objStartDate,objEndDate 'ADO CONNECTION Set connShape = Server.CreateObject("ADODB.Connection") connShape.Provider = "MSDataShape" 'Tell ADO to expect MSShape Command in SQL Syntax connShape.Open "DSN=NAME-OF-ODBC-SOURCE" 'Insert your Data Source Name String 'ADO RECORDSET Set objRS = Server.CreateObject("ADODB.Recordset") 'Shape SQL Syntax [Available at Microsoft KB Article Q189657] 'WHY DID I USE SHAPE AND NOT A JOIN? 'Look at the 2nd and 3rd SELECTS, I need ' ed to retrieve a record associated to 'order_id BUT the same field name "event ' _value" and different event_types. 'Working Example --&gt; strShape = "SHAPE {Select order_id,f_name,l_name FROM Order_Table"&_ " WHERE l_name = 'SMITH' ORDER BY l_name} AS OrderData "&_ "APPEND "&_ "({ SELECT order_id, event_value, event_type FROM event" &_ " WHERE event_type = 'UserStartDate' } " &_ " RELATE order_id TO order_id) AS STARTDATE, "&_ " ({ SELECT order_id, event_value, event_type FROM event" &_ " WHERE event_type = 'UserEndDate' } " &_ " RELATE order_id TO order_id) AS ENDDATE" 'Open RecordSet objRS.OPEN strShape,ConnShape,adOpenForwardOnly Response.Write "<table border="1" cellpadding="0" cellspacing="0">" Do While Not objRS.EOF 'Looping through Parent Record Set 'Take from Parent Record Set Response.Write("<tr><td>" & objRS("order_id") & "</td>") Response.Write("<td>" & objRS("l_name") & ",&nbsp;" & objRS("f_name") & "</td>") 'StartDate 1st Child RecordSet No Loop, ' EXPECTING ONLY ONE RECORD VALUE 'Must use "STARTDATE" as reference in SQL "AS STARTDATE" Set objStartDate = objRS("STARTDATE").Value If objStartDate.Eof = True Then Response.Write("<td>&nbsp;</td>") Else Response.Write("<td>" & objStartDate("event_value") & "</td>") End If objStartDate.Close 'EndDate 2nd Child RecordSet Loop Used,E ' XPECTING MORE MULTIPLE RECORD VALUES 'Must use "ENDDATE" as reference in SQL "AS ENDDATE" Set objEndDate = objRS("ENDDATE").Value If objEndDate.Eof = True Then Response.Write("<td>&nbsp;</td>") Else Response.Write("<td>") while not objEndDate.Eof Response.write (objEndDate("event_value") & ",") objEndDate.MoveNext wend Response.Write("</td>") End If objEndDate.Close Response.Write "</tr>" objRS.MoveNext LOOP Response.Write "</table>" 'Clean Up connShape.Close objRS.Close Set connShape = Nothing Set objRS = Nothing %&gt;