SQL inline if statement type question

Question

Hi, I'd like to make an (MS)SQL query that returns something like this:

Col1 Col2                  Col3
---- --------------------- ------
AAA  18.92                 18.92
BBB  20.00                 40.00
AAA  30.84                 30.84
BBB  06.00                 12.00
AAA  30.84                 30.84
AAA  46.79                 46.79
AAA  86.40                 86.40

where Col3 is equal to Col2 when Col1 = AAA and Col3 is twice Col2 when Col1 = BBB. Can someone point me in the rigth direction please?

Answer 1

You didn't mention what kind of database you're using. Here's something that will work in SQL Server:

SELECT Col1, Col2, 
    CASE WHEN Col1='AAA' THEN Col2 WHEN Col1='BBB' THEN Col2*2 ELSE NULL END AS Col3
FROM ...

Answer 2

select *
from yourtable
where (Col3 = col2 AND Col1 = 'AAA') OR
    (Col3 = (2*Col2) AND Col1='BBB')

Answer 3

It depends on your flavor of SQL. Case/When works with SQL Server (and possibly others).

Select Col1, Col2, 
       Case When Col1 = 'AAA' Then Col2 Else Col2 * 2 End As Col3
From   YourTable

Answer 4

Great, thanks. I edited my qustion to specift MSSQL. The CASE clause is more what I was looking for.