Webmaster Forum / ASP / Database Access / January 2007

i am new to asp/sql and of course, i have some problems with it.

please let me explain:
i have a problem with a sql result and its output in asp

my sql result table looks like this

city    date        payment        total
================================================
city1    1/1/2007    Visa        123.32
city1    1/1/2007    Mastercard    23.30
city1    1/1/2007    AMEX        40.30
city1    1/2/2007    Mastercard    12.30
city1    1/2/2007    AMEX        234.20
city2    1/1/2007    Visa        111.11
city2    1/1/2007    AMEX        222.22
city2   1/3/2007    Visa        233.33
city3    1/1/2007    Visa        123.23
city3    1/1/2007    Mastercard    45.34
....
and so forth

there are 3 fix payment options I want to show always: Visa, Mastercard
and AMEX.

I would like to output this result table shown before as followed in
asp:

First sorted by city, then date, and then always followed by 3 fixed
columns for the payment types

City1        Visa        Mastercard    Amex
-------------------------------------------------------------------
1/1/2007    123.32        23.30        40.30
1/2/2007    -        12.30        234.20

next city

City2        Visa        Mastercard    Amex
-------------------------------------------------------------------
1/1/2007    111.11        -        222.22
1/3/2007    233.33        -        -

next city

City3        Visa        Mastercard    Amex
-------------------------------------------------------------------
1/1/2007    111.11        -        222.22
1/3/2007    233.33        -            -

I tried it with looping while checking if date and city changed.
however, every time i try it, i can't show it properly like the way i
want it.

Please, can somebody help?

If you need more information, please let me know.
thank you so much.