--- In firebird-support@yahoogroups.com, "Tanz Anthrox" wrote:

> Hi,
>
> I have an example and I could not find a solution for this
>
> Table
>
> Name Type Amount
> Food Outcome 500
> Salary Income 2500
> Clothes Outcome 50
> Rental Income 300
> Car Outcome 80
> Oil Outcome 50
> ...
>
> and I need a result set for this like,
>
> Name Amount Name Amount
> Food 500 Salary 2500
> Clothes 50 Rental 300
> Car 80 null null
> Oil 50 null null
>
>
> 2 column for Outcome and 2 column for income,
>
> Pls, Becareful outcome count and income count may be different.

I think you have to write a stored procedure. Either that, or add a
new column to your table, e.g.

Name Type Amount IONo
Food Outcome 500 1
Salary Income 2500 1
Clothes Outcome 50 2
Rental Income 300 2
Car Outcome 80 3
Oil Outcome 50 4

and then do

SELECT T1.Name, T1.Amount, T2.Name, T2.Amount
FROM Table T1
FULL JOIN Table T2 on T2.IONo = T1.IONo
WHERE T1."Type" = 'Outcome' AND
T2."Type" = 'Income'

Note that it will be difficult to make this "multiusersafe", since
IONo must be a consecutive number without duplicates within each Type.

HTH,
Set