Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    44

    Unanswered: Transferring data from one table where doesnt exist in one

    Hi guys,

    I have TABLE1 and TABLE2


    TABLE1 (ref,RESOURCE01,RESOURCE02,RESOURCE03,RESOURCE04,R ESOURCE05)

    TABLE2 (ref,RESOURCE01,RESOURCE02,RESOURCE03,RESOURCE04,R ESOURCE05)

    If TABLE2 has a data in resource 01-05 that isn't in resource01-05 of TABLE1 then I want to added it to the next free slot where ref is the unique key.

    Note TABLE2 doesnt have to have all of TABLE1


    eg if

    TABLE1

    01 AAA BBB
    02 CCC DDD EEE
    03 AAA DDD

    TABLE2

    01 AAA CCC
    02 CCC DDD EEE FFF
    03

    I would like TABLE1 to be updated to appear like

    TABLE1

    01 AAA BBB CCC
    02 CCC DDD EEE FFF
    03 AAA DDD

    Where do I even start here?

    Thanks,
    Conor

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You start by redesigning your table structure.
    What happens now if you wanted to add a 6th resource for one ref?
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2011
    Posts
    44
    Thanks GVEE,

    This is a 3rd party software and I can't modify the table structure. I am aware if further fields are added then the update will need to change but this isn't necessary for now so it needs to work on the existing table structure.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Horrible data structure = horrible queries.
    Code:
    DECLARE @t1 table (
       ref        char(2)
     , resource01 char(3)
     , resource02 char(3)
     , resource03 char(3)
     , resource04 char(3)
     , resource05 char(3)
    );
    
    INSERT INTO @t1 (ref, resource01, resource02, resource03, resource04, resource05)
     VALUES ('01', 'AAA', 'BBB',  NULL, NULL, NULL)
          , ('02', 'CCC', 'DDD', 'EEE', NULL, NULL)
          , ('03', 'AAA', 'DDD',  NULL, NULL, NULL)
    ;
    
    DECLARE @t2 table (
       ref        char(2)
     , resource01 char(3)
     , resource02 char(3)
     , resource03 char(3)
     , resource04 char(3)
     , resource05 char(3)
    );
    
    INSERT INTO @t2 (ref, resource01, resource02, resource03, resource04, resource05)
     VALUES ('01', 'AAA', 'CCC',  NULL,  NULL, NULL)
          , ('02', 'CCC', 'DDD', 'EEE', 'FFF', NULL)
          , ('03',  NULL,  NULL,  NULL,  NULL, NULL)
    ;
    
    ; WITH t1 AS (
      SELECT ref
           , resource01 As resource
      FROM   @t1
      WHERE  resource01 IS NOT NULL
      UNION ALL
      SELECT ref
           , resource02 As resource
      FROM   @t1
      WHERE  resource02 IS NOT NULL
      UNION ALL
      SELECT ref
           , resource03 As resource
      FROM   @t1
      WHERE  resource03 IS NOT NULL
      UNION ALL
      SELECT ref
           , resource04 As resource
      FROM   @t1
      WHERE  resource04 IS NOT NULL
      UNION ALL
      SELECT ref
           , resource05 As resource
      FROM   @t1
      WHERE  resource05 IS NOT NULL
    )
    , t2 AS (
      SELECT ref
           , resource01 As resource
      FROM   @t2
      WHERE  resource01 IS NOT NULL
      UNION ALL
      SELECT ref
           , resource02 As resource
      FROM   @t2
      WHERE  resource02 IS NOT NULL
      UNION ALL
      SELECT ref
           , resource03 As resource
      FROM   @t2
      WHERE  resource03 IS NOT NULL
      UNION ALL
      SELECT ref
           , resource04 As resource
      FROM   @t2
      WHERE  resource04 IS NOT NULL
      UNION ALL
      SELECT ref
           , resource05 As resource
      FROM   @t2
      WHERE  resource05 IS NOT NULL
    )
    , combined AS (
      SELECT Coalesce(t1.ref, t2.ref) As ref
           , Coalesce(t1.resource, t2.resource) As resource
      FROM   t1
       FULL
        JOIN t2
          ON t2.ref = t1.ref
         AND t2.resource = t1.resource
    )
    , sequenced AS (
      SELECT ref
           , resource
           , Row_Number() OVER (PARTITION BY ref ORDER BY resource) As sequence
      FROM   combined
    )
    SELECT ref
         , Max(CASE WHEN sequence = 1 THEN resource END) As resource01
         , Max(CASE WHEN sequence = 2 THEN resource END) As resource02
         , Max(CASE WHEN sequence = 3 THEN resource END) As resource03
         , Max(CASE WHEN sequence = 4 THEN resource END) As resource04
         , Max(CASE WHEN sequence = 5 THEN resource END) As resource05
    FROM   sequenced
    GROUP
        BY ref
    Result:
    Code:
    ref  resource01 resource02 resource03 resource04 resource05
    ---- ---------- ---------- ---------- ---------- ----------
    01   AAA        BBB        CCC        NULL       NULL
    02   CCC        DDD        EEE        FFF        NULL
    03   AAA        DDD        NULL       NULL       NULL
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2011
    Posts
    44
    Thanks George, I didn't expect it would be pretty!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •