Picking from a column, with different criterias, to one table - SQL Oracle

Question

I have a table, where from one of columns, data should be picked in accordance to another column. It's probably best to show.

My DB:

Expected result:

So I tried to do it with join, but it's probably wrong, since it all comes from one table. I got stuck at this point:

SELECT b1.area as line, b2.budget/12 as flow_MTD, b2.budget as flow_BUDGET,
b1.budget/12 as investment_products_MTD, b1.budget as 

investment_products_BUDGET,
b3.budget/12 as bonds_MTD, b3.budget as bonds_BUDGET,
b4.budget/12 as structure_MTD, b4.budget as structure_BUDGET    

    FROM budgets b1

    JOIN budgets b2 ON b1.area=b2.area

    JOIN budgets b3 ON b1.area=b3.area
    JOIN budgets b4 ON b1.area=b4.area

    where b1.limit = 'main'
    and b1.neighbourhood = 'alpha'
    and b2.neighbourhood = 'beta'

    and b3.neighbourhood = 'delta'
    and b4.neighbourhood = 'gamma'

What would be a proper way to do this..?

EDIT/UPDATE: Since my question has been marked as possibly solved by Oracle SQL pivot query .. that solution seems not to work, unless it could be corrected:

SELECT SUM(CASE WHEN t.neighbourhood = 'alpha' THEN t.value ELSE 0 END) AS alpha,
       SUM(CASE WHEN t.neighbourhood = 'beta' THEN t.value ELSE 0 END) AS beta,
       SUM(CASE WHEN t.neighbourhood = 'delta' THEN t.value ELSE 0 END) AS delta,
       SUM(CASE WHEN t.neighbourhood = 'gamma' THEN t.value ELSE 0 END) AS gamma
  FROM budgets t

It is called _pivot_. Search some questions on SO using terms "oracle" and "pivot" — Florin Ghita, Oct 13 '15 at 10:40
Possible duplicate of [Oracle SQL pivot query](http://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — Florin Ghita, Oct 13 '15 at 10:42
@FlorinGhita Thank you for the advice. However, for some reason replacing that data with mine, causes an error.. `SELECT SUM(CASE WHEN t.neighbourhood = 'alpha' THEN t.value ELSE 0 END) AS alpha, SUM(CASE WHEN t.neighbourhood = 'beta' THEN t.value ELSE 0 END) AS beta, SUM(CASE WHEN t.neighbourhood = 'delta' THEN t.value ELSE 0 END) AS delta, SUM(CASE WHEN t.neighbourhood = 'gamma' THEN t.value ELSE 0 END) AS gamma FROM budgets t ` — Andy Vincent, Oct 13 '15 at 11:19
@FlorinGhita So my question has been redirected to this http://stackoverflow.com/questions/4841718/oracle-sql-pivot-query, which doesn't help me, because I can't fit my syntax to it. And no one will help me, since that question is not mine and mine is redirected (dead). The only thing I can do, is to post a new question, so similar to both of these, that would probably be also reported as duplicate. Great, thank you :) — Andy Vincent, Oct 13 '15 at 12:36
Have you tried also the pivot syntax: [http://stackoverflow.com/a/4841846/319875](http://stackoverflow.com/a/4841846/319875)? — Florin Ghita, Oct 13 '15 at 12:41
@FlorinGhita By now I have it almost solved, so will keep working on the first syntax. It's ok that you called it a duplicate, I think it's just that there's something missing in SOF procedure in cases like this. Anyway, thanks for trying to help. — Andy Vincent, Oct 13 '15 at 13:16
The accepted answer syntax of that question is simpler. Seem you do not have a `t.value` in your table. — Florin Ghita, Oct 13 '15 at 13:19

Kirill Kin · Answer 1 · 2015-10-13T11:38:29.937

This is what are you looking for

SELECT b.area,
       v.total_alpha / 12 AS monthly_alpha,
       v.total_alpha,
       v.total_beta / 12 AS monthly_beta,
       v.total_beta,
       v.total_gamma / 12 AS monthly_gamma,
       v.total_gamma,
       v.total_delta / 12 AS monthly_delta,
       v.total_delta,
       v.total_alpha + v.total_beta + v.total_gamma + v.total_delta AS total
FROM (SELECT b.area,
             SUM(DECODE(b.neighbourhood, 'alpha', b.budget, 0)) AS total_alpha,
             SUM(DECODE(b.neighbourhood, 'beta', b.budget, 0)) AS total_beta,
             SUM(DECODE(b.neighbourhood, 'gamma', b.budget, 0)) AS total_gamma,
             SUM(DECODE(b.neighbourhood, 'delta', b.budget, 0)) AS total_delta
      FROM budgets b
      GROUP BY b.area
     ) v

Picking from a column, with different criterias, to one table - SQL Oracle

1 Answers1