Combining multiple-same records with different values into one record.

  • Hello Folks,

    I have a question about combining multiple records with same ID but different values between records on different fields into a single record with all values combined for all fields. Example:

    Multiple records with the same record ID:

    RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1

    1380900 XZ01 12 1/6/2015 P302

    1380900 XZ01 12 1/6/2015 L405

    1380900 XZ01 12 1/6/2015 P302 1004

    INTO a single record:

    RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1

    1380900 XZ01 12 1/6/2015 P302 L405 1004

    Please see attachment for a better picture of the table above.

    Thank you very much!

    R.

  • Given the data, this works:

    SELECT RecID

    , BO_ID

    , Code

    , Code_Date

    , MAX(CD1) AS Msg1

    , MAX(CD2) AS Msg2

    , MAX(CD3) AS Msg3

    FROM

    (SELECT 1380900 AS RecID

    ,'XZ01' AS BO_ID

    , 12 AS Code

    , '1/16/2015' AS Code_Date

    , 'P302' AS CD1

    , NULL AS CD2

    , NULL AS CD3

    UNION ALL

    SELECT 1380900 AS RecID

    ,'XZ01' AS BO_ID

    , 12 AS Code

    , '1/16/2015' AS Code_Date

    , NULL AS CD1

    , 'L405' AS CD2

    , NULL AS CD3

    UNION ALL

    SELECT 1380900 AS RecID

    ,'XZ01' AS BO_ID

    , 12 AS Code

    , '1/16/2015' AS Code_Date

    , NULL AS CD1

    , NULL AS CD2

    , 1004 AS CD3) X

    GROUP BY RecID

    , BO_ID

    , Code

    , Code_Date;

    That said, you'll get much better and tested answers if you learn to post your questions according to best practices[/url]. Jeff Moden wrote a great article on it, and it's well worth your time to read it.

  • Thank you very much! that worked great!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply