Re: SQL help needed

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/11/03
Message-ID: <63jqj4$l9g@bgtnsc02.worldnet.att.net>#1/1

Try to use this query in your view:

SELECT t1.CustNo, "Jan97", "Feb97", ...
FROM tbl t1, (SELECT CustNo, SUM(Amount) "Jan97" FROM tbl WHERE TRUNC(Day) BETWEEN '01-Jan-97' AND '31-Jan-97' GROUP BY CustNo) t2, (SELECT CustNo, SUM(Amount) "Feb97" FROM tbl WHERE TRUNC(Day) BETWEEN '01-Feb-97' AND '28-Feb-97' GROUP BY CustNo) t3,..... WHERE t1.CustNo=t2.CustNo
AND t1.CustNo=t3.CustNo
........

Hope this helps.

Michael Serbanescu

---

On Sun, 02 Nov 1997 06:27:11 -0500, "Michael G. Schneider" <mgs_software_at_compuserve.com> wrote:

>Given the following table (customers with payment on a certain day)
>
> CREATE TABLE tbl
> (
> CustNo NUMBER,
> Day DATE,
> Amount NUMBER
> )
>
>Is it then possible to create a view, which shows ...
>
> - vertically: the customers
> - horizontal: the sum of the amounts for one month
>
>So one row should give one customer with 12 fields 'Jan97', 'Feb97', ...
>giving the totaled amount for that month.
>
>Michael G. Schneider
>
>mgs_software_at_compuserve.com
Received on Mon Nov 03 1997 - 00:00:00 CST