Grouping Data by Adrian Forbes

Something people ask for a lot is code to turn data like examples listed below.  Find enclosed group.asp while demonstrates a few ways to do this. It shows the raw data, groups it as plain text, in a table and in an unordered list.

Raw Data

Parent 1 Child 1
Parent 1 Child 2
Parent 1 Child 3
Parent 2 Child 1
Parent 2 Child 2
Parent 2 Child 3
Parent 3 Child 1
Parent 3 Child 2
Parent 3 Child 3

Into

Parent 1
Child 1
Child 2
Child 3
Parent 2
Child 1
Child 2
Child 3
Parent 3
Child 1
Child 2
Child 3

Here is the code

<!-- Author: Adrian Forbes -->
<h1>Raw Data</h1>
<%
    set objRS = createobject("ADODB.Recordset")
objRS.Open "select Parents.ID, Parents.strParent, Children.strChild from Parents join Children on Children.intParent = Parents.ID ORDER BY Parents.ID", "dsn=TestDB;uid=sa;pwd=;"

    while not objRS.EOF
        Response.Write objRS("strParent") & " " & objRS("strChild") & "<br>" & vbCRLF
        objRS.MoveNext
    wend
    objRS.Close
    set objRS.ActiveConnection = nothing
    set objRS = nothing
%>

<h1>Plain text</h1>
<%
    set objRS = createobject("ADODB.Recordset")
objRS.Open "select Parents.ID, Parents.strParent, Children.strChild from Parents join Children on Children.intParent = Parents.ID ORDER BY Parents.ID", "dsn=TestDB;uid=sa;pwd=;"

    sPrevParent = ""
    sParent = ""
    while not objRS.EOF
        sPrevParent = sParent
        sParent = objRS("strParent")
        if strcomp(sPrevParent, sParent) <> 0 then
            Response.Write "<p><b>" & sParent & "</b></p>" & vbCRLF
        end if
        Response.Write objRS("strChild") & "<br>" & vbCRLF
        objRS.MoveNext
    wend
    objRS.Close
    set objRS.ActiveConnection = nothing
    set objRS = nothing
%>

<h1>Table</h1>
<table border=0>
<%
    set objRS = createobject("ADODB.Recordset")
objRS.Open "select Parents.ID, Parents.strParent, Children.strChild from Parents join Children on Children.intParent = Parents.ID ORDER BY Parents.ID", "dsn=TestDB;uid=sa;pwd=;"

    sPrevParent = ""
    sParent = ""
    while not objRS.EOF
        sPrevParent = sParent
        sParent = objRS("strParent")
        Response.Write "<tr>"
        if strcomp(sPrevParent, sParent) <> 0 then
            Response.Write "<td>" & sParent & "</td>" & vbCRLF
        else
            Response.Write "<td>&nbsp;</td>" & vbCRLF
        end if
        Response.Write "<td>" & objRS("strChild") & "</td></tr>" & vbCRLF
        objRS.MoveNext
    wend
    objRS.Close
    set objRS.ActiveConnection = nothing
    set objRS = nothing
%>
</table>

<h1>Unordered list</h1>
<ul>
<%
    set objRS = createobject("ADODB.Recordset")
objRS.Open "select Parents.ID, Parents.strParent, Children.strChild from Parents join Children on Children.intParent = Parents.ID ORDER BY Parents.ID", "dsn=TestDB;uid=sa;pwd=;"

    sPrevParent = ""
    sParent = ""
    bFirst = true
    while not objRS.EOF
        sPrevParent = sParent
        sParent = objRS("strParent")
        if strcomp(sPrevParent, sParent) <> 0 then
            if not bFirst then
                Response.Write "</ul>" & vbCRLF
            end if
            bFirst = false
            Response.Write "<li>" & sParent & vbCRLF & "<ul>" & vbCRLF
        end if
        Response.Write "<li>" & objRS("strChild") & vbCRLF
        objRS.MoveNext
    wend
    objRS.Close
    set objRS.ActiveConnection = nothing
    set objRS = nothing
%>
</ul>
</ul>
</table>

blog comments powered by Disqus