none
join the same table and insert values in different columns RRS feed

  • Question

  • Hi,

    I would like to compare values in the same table and get the single record with different values in the multiple columns.


    For table tab1, ID is my key column. If type1 is active (A) then i need to update X else blank on Code1 column and if type2 is active (A) then i need to update X else blank on code2 column. Both type1 and type2 comes from same table for same ID

    Below is the example to understand my scenario clearly....

    declare  @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
    
    insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
    insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
    insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
    insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')
    insert into @tab1 values ('55C', '2015-07-30', 'A', 'type1')
    insert into @tab1 values ('55C', '2015-07-30', 'A', 'type2')
    
    select * from @tab1

    Expected result

    Regards,

    Fazlu

    Friday, August 7, 2015 6:42 AM

Answers

  • Hi Fazlu,

    @Visakh16, No offense. Just fix the syntax error in your query so that OP can apply it directly.

    declare  @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
    
    insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
    insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
    insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
    insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')
    insert into @tab1 values ('55C', '2015-07-30', 'A', 'type1')
    insert into @tab1 values ('55C', '2015-07-30', 'A', 'type2')
    
    SELECT * FROM @tab1
    
    SELECT ID,
    CASE MAX(CASE   WHEN [type]='type1' THEN status END) WHEN 'A' THEN 'X' ELSE '' END AS Code1,
    CASE MAX(CASE  WHEN  [type]='type2' THEN status END) WHEN 'A' THEN 'X' ELSE '' END AS Code2
    FROM @tab1
    GROUP BY ID

    @Fazlu, I don't see where the column code1 or code2 exists. If you purpose is to update some table which has those 2 columns besides a related ID, you may see below.

    declare  @CodeTbl table (ID varchar(20), code1 varchar(1), code2 varchar(10))
    insert into @CodeTbl(id) values('55C')
    
    ;WITH Cte AS
    (
    SELECT ID,
    CASE MAX(CASE   WHEN [type]='type1' THEN status END) WHEN 'A' THEN 'X' ELSE '' END AS Code1,
    CASE MAX(CASE  WHEN  [type]='type2' THEN status END) WHEN 'A' THEN 'X' ELSE '' END AS Code2
    FROM @tab1
    GROUP BY ID
    )
    UPDATE ct SET code1=c.Code1,code2=c.Code2 -- or insert or maybe better a merge 
    FROM
    Cte c JOIN @CodeTbl ct
    	ON c.ID=ct.ID
    select * from @CodeTbl



    Eric Zhang
    TechNet Community Support

    Monday, August 10, 2015 2:44 AM
    Moderator

All replies

  • Sorry , I cannot see the picture can provide it in another way?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Friday, August 7, 2015 6:57 AM
    Answerer
  • sounds like this

    SELECT ID,
    MAX(CASE WHEN type = 'type1' THEN status END) = 'A' THEN 'X' ELSE '' END AS Code1,
    MAX(CASE WHEN type = 'type2' THEN status END) = 'A' THEN 'X' ELSE '' END AS Code2
    FROM @tab1
    GROUP BY ID
    
    
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, August 7, 2015 7:52 AM
  • Like this ?

    select * from @tab1
    pivot(max(status) for type in (type1,type2)) pvt
    


    Regards, RSingh

    Friday, August 7, 2015 8:47 AM
  • Hi Fazlu,

    @Visakh16, No offense. Just fix the syntax error in your query so that OP can apply it directly.

    declare  @tab1 table (ID varchar(20), dt date, status varchar(1), type varchar(10))
    
    insert into @tab1 values ('55A', '2015-07-30', 'A', 'type1')
    insert into @tab1 values ('55A', '2015-07-30', 'C', 'type2')
    insert into @tab1 values ('55B', '2015-07-30', 'C', 'type1')
    insert into @tab1 values ('55B', '2015-07-30', 'A', 'type2')
    insert into @tab1 values ('55C', '2015-07-30', 'A', 'type1')
    insert into @tab1 values ('55C', '2015-07-30', 'A', 'type2')
    
    SELECT * FROM @tab1
    
    SELECT ID,
    CASE MAX(CASE   WHEN [type]='type1' THEN status END) WHEN 'A' THEN 'X' ELSE '' END AS Code1,
    CASE MAX(CASE  WHEN  [type]='type2' THEN status END) WHEN 'A' THEN 'X' ELSE '' END AS Code2
    FROM @tab1
    GROUP BY ID

    @Fazlu, I don't see where the column code1 or code2 exists. If you purpose is to update some table which has those 2 columns besides a related ID, you may see below.

    declare  @CodeTbl table (ID varchar(20), code1 varchar(1), code2 varchar(10))
    insert into @CodeTbl(id) values('55C')
    
    ;WITH Cte AS
    (
    SELECT ID,
    CASE MAX(CASE   WHEN [type]='type1' THEN status END) WHEN 'A' THEN 'X' ELSE '' END AS Code1,
    CASE MAX(CASE  WHEN  [type]='type2' THEN status END) WHEN 'A' THEN 'X' ELSE '' END AS Code2
    FROM @tab1
    GROUP BY ID
    )
    UPDATE ct SET code1=c.Code1,code2=c.Code2 -- or insert or maybe better a merge 
    FROM
    Cte c JOIN @CodeTbl ct
    	ON c.ID=ct.ID
    select * from @CodeTbl



    Eric Zhang
    TechNet Community Support

    Monday, August 10, 2015 2:44 AM
    Moderator