CODEWITHSUNDEEP
sql
Jackson Lopes
Jackson Lopes

Reputation: 255

get a column based on values of 2 columns SQL

I have a table with many columns, out of these, two columns i am interested in.

These 2 column may have values like (p,p)(a,p)(p,a)(a,a).

Based on what values are present in these columns collectively, i want to display a third column as 1,0.5,0.5,0 for the cases mentioned above respectively.

eg table:

column1 column2 column3
   p       p        1
   p       a       0.5
   a       p       0.5
   a       a        0

how can this be done. i tried a lot but to no avail. plz help.

Upvotes: 1

Views: 135

Answers (2)

ah_hau
ah_hau

Reputation: 768

SELECT   column1, column2, 
     CASE 
         WHEN column1 = p AND column2 = p THEN 1
         WHEN column1 = a AND column2 = a THEN 0
         ELSE 0.5
      END AS column3
FROM Production.Product

Here you go

Upvotes: 1

Dimt
Dimt

Reputation: 2328

This is the example using CASE statement which examines values in both columns and returns the required value in the third column.

CASE WHEN column1 = 'p' AND column2 = 'p' THEN 1
     WHEN (column1 = 'a' AND column2 = 'p') OR (column1 = 'p' AND column2 = 'a') THEN 0.5
     WHEN column1 = 'a' AND column2 = 'a' THEN 0 END AS Column3

Upvotes: 2

Related Questions