CODEWITHSUNDEEP
sqlsql-server
Jonny
Jonny

Reputation: 5

How to select multiple rows with a condition of only value

I have a table Salary (ID, Name, Code_Value, Value)

ID       | Name| Code_Value | Value
---------+-----+------------+---------
D4ABD930 | A   |      XP    | 400287
6FE02B7A | B   |      AP    | 1002
ABC12343 | C   |      PC    | 10007

I want value of column "Name" is 'A', will have three rows. Every row is another value.

I try to select to get this result:

Name | Code_Value | Value
-----+------------+------------
 A   |   XP1      | 400287 *60 
 A   |   XP2      |   1002 *30
 A   |   XP3      |  10007 *15 
 C   |   PC       |  10007
 B   |   AP       |   1002

This code throws an error. I write to descript result:

select  
    Name, 
    (case 
        when Code_Value ='XP' then 'XP1'
        when Code_Value ='XP' then 'XP2'
        else 'XP3' 
     end),
    value 
from  
    Salary

Help me resolve this issue. Thank you.

Upvotes: 0

Views: 70

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use join and a derived table for this:

select s.name, coalesce(v.code_value, s.code_value) as code_value,
       s.value * coalesce(v.factor, 1)
from salary s left join
     (values ('XP', 'XP1', 60),
             ('XP', 'XP2', 30),
             ('XP', 'XP3', 15)
     ) v(old_code_value, code_value, factor)
     on v.old_code_value = s.code_value;

Upvotes: 2

Related Questions