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
– gh9, Commented Mar 3, 2014 at 14:58
@gh9 at presentation level. But why not in sql level if it would be faster — zkanoca
– zkanoca, Commented Mar 3, 2014 at 14:59
Look at nested repeaters: stackoverflow.com/questions/3571363/nested-repeaters-in-asp-net — Rick S
– Rick S, Commented Mar 3, 2014 at 15:00
How are you storing the result of your SQL query? DataTable? Custom object? — Daniel Simpkins
– Daniel Simpkins, Commented Mar 3, 2014 at 15:00

zibidyum · Accepted Answer · 2014-03-03 15:33:12Z

1

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>
}

answered Mar 3, 2014 at 15:33

zibidyum

1741 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zkanoca · Accepted Answer · 2014-03-12 07:42:39Z

0

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.

edited Mar 12, 2014 at 7:42

answered Mar 12, 2014 at 6:33

zkanoca

10k10 gold badges55 silver badges99 bronze badges

1 Comment

Toolkit Over a year ago

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

Collectives™ on Stack Overflow

Nested list of SQL query result

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related