select rows and generate a new column based on condition SQL

Question

I have the following table in Oracle

Company   Rate   Period
1A        10     sep-16
1B        20     sep-15
1C        30     oct-16
1D        40     sep-16
1A        50     oct-16
2B        50     sep-15
1C        60     oct-14

I want to select some rows and add a value based on conditions. so the result will be similar to the following:

Company   Rate   Period    Currency
1A        50     oct-16    USD
1C        30     oct-16    AED

In the previous table, I am selecting only the companies 1A, 1C where Period ='oct-16'. and I need to add a column "currency" for each company where 1A=USD, 1C=AED

what I did is:

select company, PERIOD , Rate 
from TABLE_test 
where period='Oct-16' And Company='1A' 
    OR period='Oct-16' and Company='1C';

I managed to get the companies but failed at the currency column. is this possible using SQL command? please help.

Answer 1

Yes it is possible using case condition:

    SELECT COMPANY,RATE,PERIOD,(CASE WHEN COMPANY='1A' THEN 'USD' 
                                     WHEN COMPANY='1C' THEN 'AED' END
                                    )CURRENCY
    FROM TABLE_TEST
    WHERE PERIOD='oct-16'
    AND COMPANY IN ('1A','1C')

Answer 2

select company, PERIOD , Rate,
(CASE WHEN Company ='1A' THEN 'USD' WHEN Company ='1C' THEN 'AED'  END) "Currency"
from TABLE_test where...