Yossi,

I think that the closest you will get with a SQL statement is ...

SELECT acct, amount AS withdrawal, 0000000000.00 AS deposits ;
FROM trans ;
WHERE type = 'withdrawal' ;
UNION ALL ;
SELECT acct, 0000000000.00 AS withdrawal, amount AS deposits ;
FROM trans ;
WHERE type = 'deposit' ;
INTO CURSOR temp

This will put the information into a cursor, but will not join rows like you want. I believe that I would run the above query then create another cursor to dump the results into as you like.

Doug
>Hilmar:
>
>Your SQL will sum all the deposits and withdrawas for each acct. That's not what I want. I want to show each individual withdrawal and deposit. If I have more withdrawals than deposits, then I want 0 to appear in the deposit field and vice versa.
>
>See the resulting cursor.
>
>Yossi
>
>
>
>>>Hi:
>>>
>>>I'm trying to figure how to accomplish the following with SQL:
>>>
>>>I have two tables for a bank, one the customer master and the other a transaction detail:
>>>

>>>CUST
>>>
>>>ACCT
>>>11111
>>>22222
>>>77777
>>>
>>>TRANS
>>>
>>>ACCT    AMOUNT    TYPE
>>>11111   10.00     withdrawal
>>>11111   11.00     withdrawal
>>>11111   12.00     deposit
>>>22222   20.00     withdrawal
>>>22222   21.00     deposit
>>>22222   22.00     deposit
>>>22222   23.00     deposit
>>>

>>>
>>>The resulting cursor should look thus:
>>>

>>>
>>>ACCT    WITHDRAWALS   DEPOSITS
>>>11111   10.00         12.00
>>>11111   11.00         0
>>>22222   20.00         21.00
>>>22222   0             22.00
>>>22222   0             23.00
>>>

>>>
>>>How do you do that???
>>>
>>>Thanks for the help,
>>>
>>>Yossi
>>
>>

>>select acct, sum(iif(type = "withdrawal", amount, $0)) as withdrawal,;
>>  sum(iif(type = "deposit", amount, $0)) as deposit;
>>  from MyTable;
>>  group by acct
>>

>>
>>HTH, Hilmar.