PIVOT (Oracle) - Convert multiple Rows to Columns

I had to convert multiple rows into columns today. Fetch balances from gl_balances and apply multiple conversions(say GBP,USD,AUD,HKD).

First approach:

select period_net_dr , period_net_dr*gt1.avg_rate gbp_value
, period_net_dr*gt2.avg_rate usd_value
, period_net_dr*gt3.avg_rate aud_value
, period_net_dr*gt4.avg_rate hkd_value
from gl_balances glb,
gl_translation_rates GT1,
gl_translation_rates GT2,
gl_translation_rates GT3,
gl_translation_rates GT4
where glb.period_name = 'AUG-12'
and GT1.period_name = 'AUG-12'
and GT2.period_name = 'AUG-12'
and GT3.period_name = 'AUG-12'
and GT4.period_name = 'AUG-12'
and GT1.to_currency = 'GBP'
and GT2.to_currency = 'USD'
and GT3.to_currency = 'AUD'
and GT4.to_currency = 'HKD';

The table gl_translation_rates appears 4 times in the query. So how we can do it better.

Using Oracle PIVOT feature this is how it can be done.

select period_net_dr, period_net_dr*gbp_rate,period_net_dr*usd_rate, period_net_dr*aud_rate,period_net_dr*hkd_rate from gl_balances glb,

(SELECT period_name,gbp_rate,usd_rate,aud_rate,hkd_rate
FROM (SELECT period_name,TO_CURRENCY_CODE, conversion_rate
FROM gl_translation_rates
where period_name = 'AUG-12' and set_of_books_id = 1)
PIVOT (sum(conversion_rate) FOR TO_CURRENCY_CODE IN ('GBP' gbp_rate,'USD' usd_rate,'EUR' eur_rate ,'HKD' hkd_rate,'SGD' sgd_rate,'AUD' aud_rate)) ) gl_rates

where glb.period_name = 'AUG-12'
and gl_rates.period_name = glb.period_name
and period_net_dr <> 0;

-----

As you can see the gl_rates pseudo table which I built in the view is the key and the beauty of pivot feature is it allows us to name the columns like gbp_rate etc.

So we have only one hit on the rates table. And the query is more readable