Bloomberg LP Interview Question
Software Engineer / DevelopersThat doesn't work. Your select query will return results like:
JAN, 1980, $$
JAN, 1981, $$
...
DEC, 2009, $$
The original question is asking you to transpose the results. So that you get 1 month per row with years as the columns.
The answer is to do it with a self join:
SELECT led.month, led1980.amount, led1981.amount [...] led2009.amount
FROM ledger led
JOIN ledger led1980 ON led.month=led1980.month and led1980.year=1980
JOIN ledger led1981 ON led.month=led1981.month and led1981.year=1981
[...]
JOIN ledger led2009 ON led.month=led2009.month and led2009.year=2009
Unfortunately, you can't do this dynamically using standard sql.
The following syntax won't be 100%. Somebody check cuz I'm lazy.
select l.Month, sum(Amount) ' Or average, or whatever operation u like
from LEDGER l
group by l.Month
pivot l.Year
select v.month, max(v.y_1980), max(v.y_1981),max(v.y_1982), max(v.y_1983)
from
(
select month,
decode(lg.year, '1980', lg.amount) y_1980,
decode(lg.year, '1981', lg.amount) y_1981,
decode(lg.year, '1982', lg.amount) y_1982,
decode(lg.year, '1983', lg.amount) y_1983
from ledger lg
) v
group by v.month
This will definitely work.
I dont think that there is a dynamic way of specifying 1980, 1981, ... 2009. Anyone?
- Altruist November 13, 2009