locked
Merge Two Rows into One Result Set RRS feed

  • Question

  • User-498848506 posted

    Hi,

    Thanks for looking at my question!!

    My table as two rows as per the illustration that I need to combine into one...

                    SampleID    Information1, Information2, Information3, Information4....

    Row1      12345          ABC                DEF                 GHI                 JKL

    Row2      123456       NULL              NULL              123                  890

    I need to combine the results into a single result set, with the SampleID for Row 2  so that I can address each of cells for my program.
    I can use "Substring" to trim the SampleID in Row 2 to match the SampleID in Row1... but the rest is a mystery to me?

    I need assistance to 'merge' all the data into one or another or a new row?

    RESULT   123456       ABC                DEF                  123                  890

    I would appreciate any assistance to accomplish this.

    Thank you

    Thursday, June 30, 2016 12:10 AM

Answers

  • User1559292362 posted

    Hi SmokinJoe,

    I believe that would make a difference in your response. Sorry for any confusion.

    If it is a character field. we could also use LEFT method or substring method.

    -- left
    select A.SampleID, 
    coalesce(a.information1,b.information1),
    coalesce(a.information2,b.information2),
    coalesce(a.information3,b.information3),
    coalesce(a.information4,b.information4)
    from MergeTwoRows A
    Left join MergeTwoRows B on left(A.SampleID,5) = b.SampleID
    where a.SampleID = 'abc-123456'
    
    --substring
    select A.SampleID, 
    coalesce(a.information1,b.information1) AA,
    coalesce(a.information2,b.information2) BB,
    coalesce(a.information3,b.information3) CC,
    coalesce(a.information4,b.information4) DD
    from MergeTwoRows A
    Left join MergeTwoRows B on SUBSTRING(A.SampleID,5,5) = b.SampleID 
    where A.SampleID = 'abc-123456'

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 1, 2016 6:23 AM

All replies

  • User1559292362 posted

    Hi SmokinJoe,

    I need to combine the results into a single result set, with the SampleID for Row 2  so that I can address each of cells for my program.
    I can use "Substring" to trim the SampleID in Row 2 to match the SampleID in Row1... but the rest is a mystery to me?

    If the type of filed named SampleID is 'int', we could not use substring function. Otherwise, we could use left function, And I create a demo as below for your reference.

    select A.SampleID, 
    coalesce(a.information1,b.information1),
    coalesce(a.information2,b.information2),
    coalesce(a.information3,b.information3),
    coalesce(a.information4,b.information4)
    from MergeTwoRows A
    Left join MergeTwoRows B on left(A.SampleID,5) = b.SampleID
    where a.SampleID = 123456

    Best regards,

    Cole Wu

    Thursday, June 30, 2016 3:29 AM
  • User-498848506 posted
    Hi Cole,

    Thanks for your response.

    It was my error in the example. It is a character field, e.g. SampleID is

    abc-123456.

    I believe that would make a difference in your response. Sorry for any confusion.

    Thank you
    Thursday, June 30, 2016 10:10 AM
  • User1559292362 posted

    Hi SmokinJoe,

    I believe that would make a difference in your response. Sorry for any confusion.

    If it is a character field. we could also use LEFT method or substring method.

    -- left
    select A.SampleID, 
    coalesce(a.information1,b.information1),
    coalesce(a.information2,b.information2),
    coalesce(a.information3,b.information3),
    coalesce(a.information4,b.information4)
    from MergeTwoRows A
    Left join MergeTwoRows B on left(A.SampleID,5) = b.SampleID
    where a.SampleID = 'abc-123456'
    
    --substring
    select A.SampleID, 
    coalesce(a.information1,b.information1) AA,
    coalesce(a.information2,b.information2) BB,
    coalesce(a.information3,b.information3) CC,
    coalesce(a.information4,b.information4) DD
    from MergeTwoRows A
    Left join MergeTwoRows B on SUBSTRING(A.SampleID,5,5) = b.SampleID 
    where A.SampleID = 'abc-123456'

    Best regards,

    Cole Wu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, July 1, 2016 6:23 AM