beta beta - 1 month ago 8
SQL Question

SQL SELECT: new column with values based on other column

I have the following SQL result from a SELECT:

col1 | col2 | col3
A | 10 | 20
B | 10 | 20
C | 10 | 20


I want to have an additional column
col4
in the results which is filled based on the following conditions:


  • if
    col1
    is
    A
    , then
    col4
    should be filled with the value from
    col2

  • if
    col1
    is
    B
    , then
    col4
    should be filled with the value from
    col3

  • if
    col1
    is
    C
    , then
    col4
    should be filled with the value
    -



The expected result therefore looks like this:

col1 | col2 | col3 | col4
A | 10 | 20 | 10
B | 10 | 20 | 20
C | 10 | 20 | -

Answer

If your columns are integers, then you shouldn't use an actual - as the result:

SELECT col1,
       col2,
       col3,
       CASE 
          WHEN col1 = 'A' THEN col2
          WHEN col1 = 'B' THEN col3
          WHEN col1 = 'C' THEN NULL
       END col4
FROM dbo.YourTable;

Now, if you do want a string as a result for col4, then you could use:

SELECT col1,
       col2,
       col3,
       CASE 
          WHEN col1 = 'A' THEN CAST(col2 AS VARCHAR(10))
          WHEN col1 = 'B' THEN CAST(col3 AS VARCHAR(10))
          WHEN col1 = 'C' THEN '-'
       END col4
FROM dbo.YourTable;
Comments