Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2012
    Posts
    1

    Unanswered: DB2 SQL merge two rows into one row

    Hi,
    I have a table with the following data:

    TABLEA
    CLI_ID ADR_TYP ADDRS CHNG_DATE PHONE
    ===================================
    A1 PR HYD 2010-01-01 123344
    A1 PR MAS 2010-05-01 123EER
    A1 M HYD1 2010-02-01 1234465
    A1 M HYD2 2010-01-01 1234467

    I want to see the result data as follows:
    CLI_ID PR_ADR_TYP ADDR PR_CHNG_DATE M_ADR_TYP M_CHNG_DATE
    ================================================== ======
    A1 PR MAS 2010-05-01 M 2010-02-01

    Note: the date is latest date for the each ADR_TYP.

    Please help me how to achieve this using DB2 SQL.
    Last edited by cjayaram; 11-22-12 at 10:06.

  2. #2
    Join Date
    Oct 2012
    Posts
    11
    Without knowing more about your data and the different adr_typs, based on your example, you can join tableA to itself to produce the desired results. I would have code similar to:
    Code:
    Select <your fields>
    from tablea a1
    join tablea a2
       on a2.cli_id = a1.cli_id
          and a2.pr_adr_typ = 'M'
    ...
    You would want to get the max date for the ID/adr_typ. You would want to do this for both sets of data. I don't know if you have a key you can use to differentiate a single record or would have to use a Fetch First Row only.
    Last edited by hookemhorb; 11-26-12 at 12:29.

Posting Permissions

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