Home > Back-end >  How to add column to an existing SQL Server table based on condition?
How to add column to an existing SQL Server table based on condition?

Time:12-21

I want to add a column to an existing table based on the condition of 3 columns in the table like the example below.

I have 3 columns :

  • EXPIRED
  • pending
  • on_the_go

where each column had as value the ID number from another table of the corresponding value (expired value is 1)(pending value is 2)...

What I want is to add a column called status that combine all these 3 columns into 1

I tried to use CASE WHEN but that did not work as expected:

SELECT 
    EXPIRED, pending, on_the_go,
    CASE EXPIRED
        WHEN 1 THEN 1
    END AS status_type,
    CASE pending
        WHEN 2 THEN 2
    END AS status_type,
    CASE on_the_go
        WHEN 3 THEN 3
    END AS status_type,
    COUNT(*) AS number_of_exchange_activities 
FROM 
    card 
GROUP BY 
    EXPIRED, pending, on_the_go
EXPIRED pending on_the_go status_type status_type status_type number_of_exchange_activities
0 2 0 NULL 2 NULL 550
0 0 3 NULL NULL 3 320
1 0 0 1 NULL NULL 310

This is what I expected to get:

EXPIRED pending on_the_go status_type number_of_exchange_activities
0 2 0 2 550
0 0 3 3 320
1 0 0 1 310

CodePudding user response:

You can use the long form of case that allows you to place full conditions in each when clause:

SELECT   expired, pending, on_the_go,
         CASE WHEN expired = 1 THEN 1
              WHEN pending = 2 THEN 2
              WHEN on_the_go = 3 THEN 3
         END AS status_type,
         COUNT(*) AS number_of_exchange_activities 
FROM     card 
GROUP BY expired, pending, on_the_go

CodePudding user response:

CASE  
    WHEN EXPIRED = 1 THEN 1 
    WHEN pending = 2 THEN 2 
    WHEN on_the_go = 3 THEN 3 
    ELSE 0
END As 'status_type'
  • Related