Combine two columns data into one column leaving null values

user_899271 0
Viewed 12068

i have table having four columns like this below

Table - subscription having data like this

  part_id     subscription   policylicense    enterpriselic
   part1        sub1          null                null
   part2        sub1          pl1                 null
   part3        sub1          null                enterpr1

I would like to get the data like this below

  part_id     subscription   license    
   part2        sub1          pl1                 
   part3        sub1          enterpr1

how to get the combined license data into one column leaving null values in the same table.. i am using sql server here

Could any one please help on this that would be very grateful to me .. Many Thanks in advance..

sql-server-2014 stored-procedures tsql sql-server sql
7 Answers

Here is another solution using COALESCE()

SELECT
    part_id
    ,subscription
    ,COALESCE(policylicense, enterpriselic) AS license
FROM subscription
WHERE COALESCE(policylicense, enterpriselic) IS NOT NULL
user_25772250 
Select 
    part_id,
    subscription,
    CONCAT(policylicense,enterpriselic) as license
from subscription where enterpriselic IS NOT NULL OR  policylicense IS NOT NULL
user_24657870

Try this:

SELECT part_id, subscription,
ISNULL(policylicense, enterpriselic) AS license
FROM yourtable
WHERE policylicense IS NOT NULL OR enterpriselicense IS NOT NULL
user_25777340

Try this. Ternary operators:

SELECT
part_id,
subscription,
IIF(policylicense IS NULL,
    IIF(enterpriselic IS NULL,'',enterpriselic),
        policylicense) as license
from subscription 
where   enterpriselic IS NOT NULL OR  
        policylicense IS NOT NULL
user_54702140

Try this COALESC()

SELECT part_id
    ,subscription
    ,COALESCE(policylicense, enterpriselic) AS license
FROM subscription
WHERE COALESCE(policylicense, enterpriselic) IS NOT NULL
user_38765650 
SELECT 
part_id ,
subscription,   
license  = ISNULL(policylicense,enterpriselic)
FROM <table>
WHERE 
policylicense IS NOT NULL OR enterpriselic IS NOT NULL
user_87325660

Here is a way using IF, so you test if the column is null or not and in having you just see the final result if null 

SELECT part_id, subscription, IF(policylicense IS NOT NULL, policylicense, IF(enterpriselic IS NOT NULL, enterpriselic, enterpriselic)) as license
FROM tableA
HAVING license IS NOT NULL;
user_56718070
Related