CODEWITHSUNDEEP
mysql
ArmMiner
ArmMiner

Reputation: 215

How to merge null values with not null values of several columns in mysql table?

I have the following example table:

 || *Column 1* || *Column 2* || *Column 3* || *Column 4* || 
 ||     a      ||   null     ||     b      ||     a      || 
 ||     b      ||     f      ||    null    ||     f      ||
 ||   null     ||     a      ||     a      ||     b      ||

The result table has to be:

 || *Column 1* || *Column 2* ||
 ||     a      ||     b      ||
 ||     b      ||     f      ||
 ||     a      ||     b      ||

Thanks!

Upvotes: 1

Views: 2443

Answers (2)

Robin Castlin
Robin Castlin

Reputation: 10996

SELECT one,
    CASE WHEN one != two THEN two
        WHEN one != three THEN three
        WHEN one != four THEN four END AS two
FROM (  SELECT COALESCE(col1, col2, col3, col4) AS one,
            COALESCE(col2, col3, col4, col1) AS two,
            COALESCE(col3, col4, col1, col2) AS three,
            COALESCE(col4, col1, col2, col3) AS four
        FROM four_columns) AS h

Can't think of a simplier solution considering what result you wish in the example.

This obviously obsoletes cases where these 4 columns have more than 2 different letters, due to only 2 columns existing. But it won't show duplicates and will not show NULL if there is at least 2 unique letters.

Upvotes: 4

Gus Monod
Gus Monod

Reputation: 110

If I understand you well, what you want to do is merge Column 3 with Column 1, and Column 4 with Column 2.

The following problem has already been answered on this post.

Upvotes: 1

Related Questions