merge 2 column into 1

Question

Hi Expert, create table table11( col1 char,col2 int,col3 int)

Insert-------------------------------------- 
insert into table11 values(1, 2,3) insert into table11 values(2, 4,6) insert into table11 values(3, 6,9) insert into table11 values(4, 8,12) 

----case condition----------------------------------------------------

case when col1=2 then col2 =0 
case when col1=3 then col2 =0 
case when col1=1 then col3 =0 
case when col1=4 then col3 =0

expected output: col1 and col2 should be merged via removing the 0 values

Answer 1

Hello If I understand, you are trying to create a table and insert values into it using SQL. However, the CASE statements you provided are not valid SQL syntax. To update the values in the table based on certain conditions, you can use an UPDATE statement with a CASE expression. Here’s an example that should achieve the desired result:

CREATE TABLE table11 (
    col1 CHAR,
    col2 INT,
    col3 INT
);

INSERT INTO table11 VALUES (1, 2, 3);
INSERT INTO table11 VALUES (2, 4, 6);
INSERT INTO table11 VALUES (3, 6, 9);
INSERT INTO table11 VALUES (4, 8, 12);

UPDATE table11
SET col2 = CASE
    WHEN col1 = 2 THEN 0
    WHEN col1 = 3 THEN 0
    ELSE col2
END,
col3 = CASE
    WHEN col1 = 1 THEN 0
    WHEN col1 = 4 THEN 0
    ELSE col3
END;

I hope this helps Alistair

Answer 2

If there are no other "0", then try this:

select 
	col1,
	col2 = coalesce(case when col1 not in (2, 3) then col2 end, case when col1 not in (1, 4) then col3 end) 
from table11
order by col1

Answer 3

How to merge this 2 column post changes

Answer 4

Hi @Shambhu Rai

Try this query:

select col1
      ,case when col1=2 or col1=3 then col3  
	        when col1=1 or col1=4 then col2 end as col2
from table11

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.