How to query for master-detail output on same page

I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.

OrderNo OrderDate

1001 7/27/2007

+ <div to show/hide>

Item Description

AAA desc_for_itemAAA

BBB desc_for_itemBBB

+ </div>

1002 7/26/2007

+ <div to show/hide>

Item Description

CCC desc_for_itemCCC

+ </div>

I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?

It’s always better to do these rendering on the ASP/UI page,

For your courtesy,

Code Snippet

Create Table #order (

[OrderNo] int ,

[OrderDate] datetime

);

Insert Into #order Values('1001','7/27/2007');

Insert Into #order Values('1002','7/26/2007');

Create Table #orderdetails (

[OrderNo] int ,

[Item] Varchar(100) ,

[Description] Varchar(100)

);

Insert Into #orderdetails Values('1001','AAA','desc_for_itemAAA');

Insert Into #orderdetails Values('1001','BBB','desc_for_itemBBB');

Insert Into #orderdetails Values('1002','CCC','desc_for_itemCCC');

Select

OrderNo,

OrderDate,

Replace(Replace((Select '<td>' + Item + '</td><td>' + Description + '</td>' as [text()] From #orderdetails OD Where OD.[OrderNo] = O.[OrderNo] For XML Path('tr')),'&gt;','>'),'&lt;','<')

From

#order O

<!--

On ASP/UI page,

While NOT rs.eof

Response.Write(“<tr><td>”)

Response.Write(rs(0))

Response.Write(“</td><td>”)

Response.Write(rs(1))

Response.Write(“</td></tr><tr>”)

Response.Write(“<td colspan=2><span style='cursor:hand' click=ExpandOrCollopse(‘div_” + rs(0) + “’)>+</span>”)

Response.Write (“<div style='display:none' id=’div_”)

Response.Write(rs(0))

Response.Write(“’><table>”)

Response.Write(rs(2))

Response.Write(“</table></div>”)

Wend

-->

|||

What you are proposing is clearly a presentation side issue and should not be 'forced' on SQL Server.

I suggest that you may find utility in the new XML output functionality.

|||

Manivannan, thanks for the sample code. I was able to create an output as you explained.

Arnie, can you point me to an easy to understand resource for this xml output functionality? I haven't really had any exposure to xml.

|||

Here are a couple of resources to get you started:

http://www.topxml.com/sql/

http://technet.microsoft.com/en-us/library/ms175024.aspx

Using XML data is perfect for web applications.