Listing Category then Items

harpua · December 11th, 2003, 07:58 PM

Can somebody please give me some advise on how to implement this, I can do the easy SQL statements, but I get lost on the complex ones and what I've tried doesn't work or I know there is a better way, I just don't know it. I am using ASP, SQL Server. Right now I have one table where I want to select all the Categories and Items and have them display as in my sample, without the categories repeating.

SELECT Category, Item FROM Table1 WHERE Start_Date <= '" & Date & "';

This is how I would like it to display
    Category_01
        Item_01
        Item_02

    Category_02
        Item_01
        Item_02
        Item_03
        Item_04

Can this work with one table or should I make another table then select the category and do some kind of Join statement, also some Categories might not have any Items for that day.

Any help would be appreciated, Thanks in advance.

pgtips · December 12th, 2003, 06:40 AM

Ok, so you've got the category and item in the same recordset. Your SQL is fine, you need to do the grouping in your asp code. You just need to keep track of the category as you loop through the recordset and only output the category when it changes, something like this:

Code:

sCatPrevious = ""
Do While Not rs.EOF

  sCatCurrent = "" & rs.Fields("Category").Value
  If sCatCurrent <> sCatPrevious Then
    ' output the category
    Response.Write sCatCurrent

    ' update the "previous" variable
    sCatPrevious = sCatCurrent

  End If

  ' output the item
  Response.Write "" & rs.Fields("Item").Value

  rs.MoveNext
Loop

pgtips · December 12th, 2003, 06:43 AM

Just noticed that you need an ORDER BY clause in your SQL to ensure that the Categories are sorted in your recordset

Code:

"SELECT Category, Item FROM Table1 WHERE Start_Date <= '" & Date & "' ORDER BY Category, Item";

harpua · December 12th, 2003, 11:47 AM

Thanks a million. You saved me a lot of time.