merge 2 column into 1

Shambhu Rai 1,406 Reputation points
2023-04-21T15:33:52.59+00:00

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 User's image

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,897 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,826 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,467 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
{count} votes

Accepted answer
  1. Alistair Ross 7,101 Reputation points Microsoft Employee
    2023-04-21T15:50:20.61+00:00

    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


3 additional answers

Sort by: Most helpful
  1. Viorel 113K Reputation points
    2023-04-21T16:00:28.7233333+00:00

    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
    
    0 comments No comments

  2. Shambhu Rai 1,406 Reputation points
    2023-04-21T19:00:37.9733333+00:00

    How to merge this 2 column post changes

    0 comments No comments

  3. CosmogHong-MSFT 23,796 Reputation points Microsoft Vendor
    2023-04-24T03:08:56.7766667+00:00

    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.

    0 comments No comments