CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlsql-server-2008
The beginner
The beginner

Reputation: 622

Selecting a column from table based on value stored in table

I have a table #A like this:

id  | main_value |  value1 |  Value2 |  value3 |    value4
------------------------------------------------------------
1   | VALUE1     |  1.00   |  0.00   |  0.00   |    2.00
2   | VALUE2     |  0.00   |  4.00   |  0.00   |    0.00
3   | VALUE4     |  0.00   |  0.00   |  1.00   |    5.00
4   | VALUE3     |  1.00   |  0.00   |  2.00   |    0.00

Each time only two values inserted in table (only two column from value1, value2, value3, value4)
I want to select only two values based on main value.
If my main value is value1 then value of 1st column is values1 and the value of the 2nd column is the other column which is non zero.

If my main value is value2 then value of 1st column is values2 and the value of the 2nd column is the other column which is non zero.

Sample data:

CREATE TABLE #A (id int, 
main_value Nvarchar(MAX), 
value1 DECIMAL(15,2), 
Value2 DECIMAL(15,2), 
value3 DECIMAL(15,2), 
value4 DECIMAL(15,2))

INSERT INTO #A VALUES(1, 'VALUE1',1,0,0,2 )
INSERT INTO #A VALUES(2, 'VALUE2',0,4,0,0 )
INSERT INTO #A VALUES(3, 'VALUE4',0,0,1,5 )
INSERT INTO #A VALUES(4, 'VALUE3',1,0,2,0 )

SELECT * FROM #A

Expected results:

id  | main_value |  1st    |  2nd    |  
--------------------------------------
1   | VALUE1     |  1.00   |  2.00   |
2   | VALUE2     |  4.00   |  1.00   |
3   | VALUE4     |  5.00   |  1.00   |
4   | VALUE3     |  2.00   |  1.00   |

Upvotes: 3

Views: 382

Answers (4)

Andrzej M.
Andrzej M.

Reputation: 102

I'm not sure which database you are using, anyway for SQL Server such solution should work:

SELECT  id,
        main_value,
           COALESCE(NULLIF(value1,0)
                   ,NULLIF(value2,0)
                   ,NULLIF(value3,0)
                   ,NULLIF(value4,0)) as Value1

        , COALESCE(NULLIF(value4,0)
                   ,NULLIF(value3,0)
                   ,NULLIF(value2,0)
                   ,NULLIF(value1,0)) as Value2

FROM #A

If in database there are nulls instead of 0 then nullif function is unnecesssary. Hope it helps.

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

Instead of writing the case expression twice, you can use cross apply:

SELECT  id, main_value, [1st],
        value1 + value2 + value3 + value4 - [1st] As [2nd]    
FROM #A
CROSS APPLY
(
    SELECT 
    CASE main_value  
        WHEN 'VALUE1' THEN value1
        WHEN 'VALUE2' THEN value2
        WHEN 'VALUE3' THEN value3
        WHEN 'VALUE4' THEN value4
    END As [1st]
) X

Upvotes: 0

iSR5
iSR5

Reputation: 3498

Do you need something like this ? (it could be improved)

SELECT 
    id
,   main_value
,   CASE main_value
    WHEN 'VALUE1' THEN value1
    WHEN 'VALUE2' THEN Value2
    WHEN 'VALUE3' THEN value3
    WHEN 'VALUE4' THEN value4
    END [1st]
,   CASE main_value
    WHEN 'VALUE1' THEN COALESCE(Value2,value3,value4)
    WHEN 'VALUE2' THEN COALESCE(Value1,value3,value4)
    WHEN 'VALUE3' THEN COALESCE(Value1,value2,value4)
    WHEN 'VALUE4' THEN COALESCE(Value1,value2,value3)
    END [2st]   
FROM (
SELECT
    id
,   main_value
,   CASE WHEN value1 = 0 THEN NULL ELSE value1 END value1
,   CASE WHEN Value2 = 0 THEN NULL ELSE Value2 END Value2
,   CASE WHEN value3 = 0 THEN NULL ELSE value3 END value3
,   CASE WHEN value4 = 0 THEN NULL ELSE value4 END value4
FROM #A
) D

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try below using case when

select id, main_value, 
case 
when main_value='VALUE1' then value1
when main_value='VALUE2' then value2
when main_value='VALUE4' then value4
when main_value='VALUE3' then value3
end as 1st,
case 
when main_value='VALUE1' then value2+value3+value4
when main_value='VALUE2' then value1+value3+value4
when main_value='VALUE4' then value1+value2+value3
when main_value='VALUE3' then value1+value2+value4
end as 2nd
from tablename

Upvotes: 2

Related Questions