Hello, I have a quick question. I'm currently using MS SQL 2005.

I have a main table which has many fields. I created a view#1 which imports all the rows from the main table but I only select the "City" field, "PurchaseDescription" field and a "Cost" field. Only the city name is unique. There are a total of 50 different city names, and each name can have more than one "PurchaseDescription" each with an associated "Cost". I would like to create a second view#2 with an added field called "Cost2". I want "Cost2" to contain the same value as the "Cost" value. However if the "PurchaseDescription" equals to "USB" then "Cost2" should be assigned the value of "Cost" multiplied by -1. If the "PurchaseDescription" content isn't equal to "USB" then "Cost2" will have the save value as "Cost".


For example,

The view#1 will have the following rows & fields (I had to pad the field with dots just to make the output look viewable on this thread)

City..............PurchaseDescription............Cost
--------------------------------------------
LA.................desk................................4.5
LA.................USB.................................5.0
LA.................USB.................................6.0
SD................chair.................................4.0
SD................door.................................10.0

The view#2 should have the following rows and fields


City..............PurchaseDescription............Cost.......Cost2
---------------------------------------------------------
LA.................desk................................4.5....... 4.5
LA.................USB.................................5.0....... -5.0
LA.................USB.................................6.0....... -6.0
SD................chair.................................4.0....... 4.0
SD................door.................................10.0....... 10.0

I don't mind if I have to use functions or more than 2 views to solve my problem. I jus need a final view that would look like view#2.

Could you please help if you can?

Thank you very much for your time and have a safe Halloween!

Try to create the view using simple where clause for checking the PurchaseDescription field and change the other fields accordingly.

Thanks debasisdas for your tip. I will try it.

think sql as calculator

select (cost * quantity) as total from mytable...