none
Query RRS feed

  • Question

  • I have two tables Test1 and Test2.

    Can you please provide query to get below output.

    Test1         Test2    
    TID TNAME TVALUE TCATEGORY   TID TNAME TCATEGORY
    1 A 0.2 CAT   1 A CATEGORY1
    2 B 0.3 CAT   3 C CATEGORY2
    3 C 0.4 2CAT   5 E CATEGORY3
    4 D 0.6 3_CAT3        
    5 E 0.5 3_CAT3        
           

    OutPut  
    TCATEGORY   TVALUE
    CATEGORY1 0.2
    CATEGORY1 0.3
    CATEGORY2 0.4
    CATEGORY3 0.6
    CATEGORY3 0.5

    Monday, January 13, 2020 8:24 PM

Answers

  • CREATE TABLE test1(
       TID       INTEGER  NOT NULL PRIMARY KEY 
      ,TNAME     VARCHAR(1) NOT NULL
      ,TVALUE    NUMERIC(3,1) NOT NULL
      ,TCATEGORY VARCHAR(6) NOT NULL
    );
    INSERT INTO test1(TID,TNAME,TVALUE,TCATEGORY) 
    VALUES (1,'A',0.2,'CAT'),
     (2,'B',0.3,'CAT'),
      (3,'C',0.4,'2CAT'),
      (4,'D',0.6,'3_CAT3'),
     (5,'E',0.5,'3_CAT3');
    
    CREATE TABLE test2(
       TID       INTEGER  NOT NULL PRIMARY KEY 
      ,TNAME     VARCHAR(1) NOT NULL
      ,TCATEGORY VARCHAR(9) NOT NULL
    );
    INSERT INTO test2(TID,TNAME,TCATEGORY) 
    VALUES (1,'A','CATEGORY1'),
      (3,'C','CATEGORY2'),
     (5,'E','CATEGORY3');
    
    
     select   Max(t2.TCATEGORY) Over(partition by t1.TCATEGORY) TCATEGORY
     , t1.TVALUE from test1 t1 
     left join test2 t2 on t1.TNAME=t2.TNAME
     Order by t1.TID
    
    
    drop table test2,test1
    

    Monday, January 13, 2020 9:07 PM
    Moderator

All replies

  • Check this script:

    declare @Test1 as table 
    (
    	TID int,
    	TNAME varchar(10),
    	TVALUE money,
    	TCATEGORY varchar(10)
    )
    
    declare @Test2 as table 
    (
    	TID int,
    	TNAME varchar(10),
    	TCATEGORY varchar(10)
    )
    
    insert into @Test1 values
    	( 1, 'A', 0.2, 'CAT' ),
    	( 2, 'B', 0.3, 'CAT' ),
    	( 3, 'C', 0.4, '2CAT' ),
    	( 4, 'D', 0.6, '3_CAT3' ),
    	( 5, 'E', 0.5, '3_CAT3' )
    
    insert into @Test2 values
    	( 1, 'A', 'CATEGORY1' ),
    	( 3, 'C', 'CATEGORY2' ),
    	( 5, 'E', 'CATEGORY3' )
    
    select t2.TCATEGORY, t1x.TVALUE
    from @Test2 as t2
    inner join @Test1 as t1 on t1.TID = t2.TID
    inner join @Test1 as t1x on t1x.TCATEGORY = t1.TCATEGORY
    order by 1, 2
    


    • Edited by Viorel_MVP Monday, January 13, 2020 9:04 PM
    • Proposed as answer by pituachMVP, Moderator Tuesday, January 14, 2020 5:26 AM
    • Marked as answer by KIRAN KUAMR Monday, January 20, 2020 6:16 AM
    • Unmarked as answer by KIRAN KUAMR Monday, January 20, 2020 6:16 AM
    Monday, January 13, 2020 9:03 PM
  • CREATE TABLE test1(
       TID       INTEGER  NOT NULL PRIMARY KEY 
      ,TNAME     VARCHAR(1) NOT NULL
      ,TVALUE    NUMERIC(3,1) NOT NULL
      ,TCATEGORY VARCHAR(6) NOT NULL
    );
    INSERT INTO test1(TID,TNAME,TVALUE,TCATEGORY) 
    VALUES (1,'A',0.2,'CAT'),
     (2,'B',0.3,'CAT'),
      (3,'C',0.4,'2CAT'),
      (4,'D',0.6,'3_CAT3'),
     (5,'E',0.5,'3_CAT3');
    
    CREATE TABLE test2(
       TID       INTEGER  NOT NULL PRIMARY KEY 
      ,TNAME     VARCHAR(1) NOT NULL
      ,TCATEGORY VARCHAR(9) NOT NULL
    );
    INSERT INTO test2(TID,TNAME,TCATEGORY) 
    VALUES (1,'A','CATEGORY1'),
      (3,'C','CATEGORY2'),
     (5,'E','CATEGORY3');
    
    
     select   Max(t2.TCATEGORY) Over(partition by t1.TCATEGORY) TCATEGORY
     , t1.TVALUE from test1 t1 
     left join test2 t2 on t1.TNAME=t2.TNAME
     Order by t1.TID
    
    
    drop table test2,test1
    

    Monday, January 13, 2020 9:07 PM
    Moderator
  • Hi KIRAN KUAMR,

    create table #Test1(TID int, TNAME varchar(10), TVALUE money, TCATEGORY varchar(10) )
    insert into #Test1 values( 1, 'A', 0.2, 'CAT' ),
    	( 2, 'B', 0.3, 'CAT' ),
    	( 3, 'C', 0.4, '2CAT' ),
    	( 4, 'D', 0.6, '3_CAT3' ),
    	( 5, 'E', 0.5, '3_CAT3' )
    
    create table #Test2 (TID int,TNAME varchar(10),TCATEGORY varchar(10) )
    insert into #Test2 values( 1, 'A', 'CATEGORY1' ),
    	( 3, 'C', 'CATEGORY2' ),
    	( 5, 'E', 'CATEGORY3' )
    
    select t2.TCATEGORY,TVALUE from #Test1 t1
    inner join #Test2 t2 on left(t1.TCATEGORY,1) =right(t2.TCATEGORY,1)
    union all
    select t2.TCATEGORY,TVALUE from #Test1 t1
    inner join #Test2 t2 on left(t1.TCATEGORY,3)=left(t2.TCATEGORY,3)
    where t2.TCATEGORY='CATEGORY1'
    order by TCATEGORY

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, January 14, 2020 5:12 AM
  • Hi KIRAN KUAMR,

    Do the answers above help you? It's so kind of you to mark helpful replies as answers in order to close this thread. By doing so, it will benefit all community members who are having this similar issue.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, January 15, 2020 5:32 AM