ShB ShB - 1 month ago 5
SQL Question

select rows and generate a new column based on condition SQL

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

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')