locked
Combine two dataset using Column1 like grouping RRS feed

  • Question

  • User-1068576637 posted

    Using Dataset1 and Dataset2 , i required output  as given in the  "Output Required" table

    Dataset 1
    Column1 Column2
    Apple Red
    Apple Green
    Apple  While
    Banana Yellow
    Banana Green
    Grapes Black
    Grapes Green
    Grapes No Color
    Grapes Red

      

    Dataset 2
    Column1 Column2
    Apple Good
    Banana Bad
    Grapes Fair

    Output - Required
    Column1 Column2
    Apple Good
       Apple     Red
       Apple     Green
      Apple     While
    Banana Bad
       Banana    Yellow
       Banana    Green
    Grapes Fair
       Grapes    Black
       Grapes    Green
       Grapes    No Color
    Grapes Red

    Thursday, January 18, 2018 4:16 AM

Answers

  • User77042963 posted
    CREATE TABLE dataset1(
       Column1 VARCHAR(6) NOT NULL 
      ,Column2 VARCHAR(8) NOT NULL
    ) 
    INSERT INTO dataset1(Column1,Column2) VALUES ('Apple','Red'),
     ('Apple','Green'),
     ('Apple','While'),
     ('Banana','Yellow'),
     ('Banana','Green'),
     ('Grapes','Black'),
     ('Grapes','Green'),
     ('Grapes','No Color'),
     ('Grapes','Red')
    
     CREATE TABLE dataset2(
       Column1 VARCHAR(6) NOT NULL 
      ,Column2 VARCHAR(8) NOT NULL
    ) 
    INSERT INTO dataset2(Column1,Column2) VALUES ('Apple','Good'),
     ('Banana','Bad'),
     ('Grapes','Fair') 
     Select Column1, Column2 from (
    select *, dense_rank() Over(order by Column1) srt, 1 as grp from dataset1
    Union All
    select *, rank() Over(order by Column1) srt , 0 as grp from dataset2
    ) t
    Order by srt, grp
    
     drop table dataset1, dataset2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 18, 2018 2:54 PM

All replies

  • User77042963 posted
    CREATE TABLE dataset1(
       Column1 VARCHAR(6) NOT NULL 
      ,Column2 VARCHAR(8) NOT NULL
    ) 
    INSERT INTO dataset1(Column1,Column2) VALUES ('Apple','Red'),
     ('Apple','Green'),
     ('Apple','While'),
     ('Banana','Yellow'),
     ('Banana','Green'),
     ('Grapes','Black'),
     ('Grapes','Green'),
     ('Grapes','No Color'),
     ('Grapes','Red')
    
     CREATE TABLE dataset2(
       Column1 VARCHAR(6) NOT NULL 
      ,Column2 VARCHAR(8) NOT NULL
    ) 
    INSERT INTO dataset2(Column1,Column2) VALUES ('Apple','Good'),
     ('Banana','Bad'),
     ('Grapes','Fair') 
     Select Column1, Column2 from (
    select *, dense_rank() Over(order by Column1) srt, 1 as grp from dataset1
    Union All
    select *, rank() Over(order by Column1) srt , 0 as grp from dataset2
    ) t
    Order by srt, grp
    
     drop table dataset1, dataset2

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 18, 2018 2:54 PM
  • User-1068576637 posted

    Great. powerful rank function. thanks for your help .

    Friday, January 19, 2018 4:37 AM