ShB ShB - 1 year ago 81
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 Source

Yes it is possible using case condition:

                                     WHEN COMPANY='1C' THEN 'AED' END
    WHERE PERIOD='oct-16'
    AND COMPANY IN ('1A','1C')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download