Nested list of SQL query result

Question

After I run my query I have got the result into a dataTable as the following (this is only a simplified resultset):

food_cat    food
-----------------------
vegit       carrot
vegit       onion
vegit       tomato
fruit       cherry
fruit       banana
fruit       orange

I want to list that result grouped by food_cat in an unordered list.

<h3> Vegit </h3>
<ul>
    <li>carrot</li>
    <li>onion</li>
    <li>tomato</ti>
</ul>
<h3>fruit</h3>
<ul>
    <li>cherry</li>
    <li>banana</li>
    <li>orange</li>
</ul>

I have tried some for, if, while controls but could not find a good solution.

Do you want to generate the UL/LI elements at the sql level or the presentation level? — gh9, Mar 03 '14 at 14:58
@gh9 at presentation level. But why not in sql level if it would be faster — zkanoca, Mar 03 '14 at 14:59
Look at nested repeaters: http://stackoverflow.com/questions/3571363/nested-repeaters-in-asp-net — Rick S, Mar 03 '14 at 15:00
How are you storing the result of your SQL query? DataTable? Custom object? — Daniel Simpkins, Mar 03 '14 at 15:00

score 1 · Answer 1 · answered Mar 03 '14 at 15:33

since you don't provide table names etc. i will try to give the answer in general way.

string previous_food_cat = '';
bool firstEntrance = true

while(trace resultSet until no elements left) 
{
    if resultSet.food_cat != previous_food_cat //check if food_cat value changed
    {
        if (!firstEntrance) //if not first entrance close the <ul> tag before opening new one
        {
            print </ul>
        }

        print <h3> resultSet.food_cat </h3>  //open new <h3> tag
        print <ul>  //open new <ul> tag


        previous_food_cat = resultSet.food_cat  //update previous_food_cat for new food_cat value
        firstEntrance = false   //set firstEntrance false so that ul tqags should be closed
    }

    print <li> resultSet.food </li>
}

zkanoca · Accepted Answer · 2014-03-12T07:42:39.187

Thank you @zibidyum, your technic obtained me to reach a solution. But final solution is here:

public bool firstcat; // defined at before Page_Load method
public int temp_cat;  // defined at before Page_Load method


for (int i = 0; i < dt.Rows.Count; i++)
{
    if (temp_cat != Convert.ToInt32(dt.Rows[i]["food_cat"]))
    {
        if (i > 0 && !firstcat)
            content.InnerHtml += "</ul>"; //solution's most critic point is here

        content.InnerHtml += "<ul>"
    }

    content.InnerHtml += String.Format("<li>{0}</li>", dt.Rows[i]["food"].ToString());

    temp_cat = Convert.ToInt32(dt.Rows[i]["food_cat"]);
}

Any better solutions, suggestions and/or ideas are welcome.

`Convert.ToInt32(dt.Rows[i]["food_cat"]))` this suck so much.... EF is a good solution but it is buggy. I was exploring oracle json seems cool, but the varchar2 limit kills everything unless you are on latest 18c — Toolkit, Oct 26 '18 at 09:48

Nested list of SQL query result

2 Answers2