asp/sql looping problem

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.

Re: asp/sql looping problem

bbinto wrote on 17 Jan 2007 08:13:10 -0800:

> 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

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


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


Look up "pivot" for the database system you are using, and it should give
you a start. What you want is a pivot table based on city+date, with payment
type as the column, and then you should find your loop much easier to
program.

Dan

Re: asp/sql looping problem

Thanks Daniel.

Really appreciate it and your hint made my day.
It all worked with Pivot tables and was after all so easy to print out.



Daniel Crichton wrote:
> bbinto wrote on 17 Jan 2007 08:13:10 -0800:
>
> > 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
>
> > 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
> >
>
>
> > 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.
>
>
> Look up "pivot" for the database system you are using, and it should give
> you a start. What you want is a pivot table based on city+date, with payment
> type as the column, and then you should find your loop much easier to
> program.
>
> Dan

Re: asp/sql looping problem

Thanks Daniel.

Really appreciate it and your hint made my day.
It all worked with Pivot tables and was after all so easy to print out.



Daniel Crichton wrote:
> bbinto wrote on 17 Jan 2007 08:13:10 -0800:
>
> > 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
>
> > 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
> >
>
>
> > 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.
>
>
> Look up "pivot" for the database system you are using, and it should give
> you a start. What you want is a pivot table based on city+date, with payment
> type as the column, and then you should find your loop much easier to
> program.
>
> Dan