1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Merging two rows based on idnetifer

Discussion in 'SQL PL/SQL' started by venu57, Jul 22, 2015.

  1. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi Guys,

    I have a table and data like in the attached Input pic.

    Input.PNG

    If the table contains single id then directly we can consider that record. But the above table contains two ids with different effective dates. We have to consider the record that is having latest effective date among two ids and merge the remaining columns details for that row.

    Sample output: Attached Output pic

    Output.PNG

    Please help on this.
     
  2. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Venu,

    Please check the following query. Hope the following query helpful for your requirement.

    Code (SQL):
    SELECT  DISTINCT id,  MAX(effective_date)effective_date,
    MAX(SELECT name FROM xx_table_name b WHERE b.id = a.id AND b.effective_date = a.effective_date AND rownum<2) name,
    MAX(SELECT job FROM xx_table_name b WHERE b.id = a.id AND b.effective_date = a.effective_date AND rownum<2) job,
    MAX(SELECT loc FROM xx_table_name b WHERE b.id = a.id AND b.effective_date = a.effective_date AND rownum<2) loc,
    MAX(SELECT grade FROM xx_table_name b WHERE b.id = a.id AND b.effective_date = a.effective_date AND rownum<2) grade,
    MAX(SELECT basic FROM xx_table_name b WHERE b.id = a.id AND b.effective_date = a.effective_date AND rownum<2) basic

    FROM xx_table_name a GROUP BY id
     
  3. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi Bharath,

    Thanks for the response. I have executed the given query, but facing the below error.

    ORA-00936: missing expression
    00936. 00000 - "missing expression"

    I have one question bharath, Can we use the select statement inside the Max function?
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I believe that the solution from Bharat could probably be made to work, but the following SQL will be a good bit more efficient than what he provided.

    Code (Text):

    WITH max_eff AS
      (SELECT id, MAX(effective_date) eff_date
       FROM   [table_name]
       GROUP BY id)
    SELECT tab1.id, tab1.effective_date, tab1.name, tab1.job, tab1.loc, tab1.grade, tab1.basic
    FROM   [table_name] tab1
           INNER JOIN max_eff
           ON tab1.id = max_eff.id
           AND tab1.effective_date = max_eff.eff_date;
     
    Bharat likes this.
  5. venu57

    venu57 Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    100
    Location:
    Bangalore
    Hi Morris,

    The given solution is working fine. But the thing here is for the latest max effective date row, for null value columns we should pick up the not null values from the less effective date row.

    Example: for Id 1234, maximum effective date is 17/07/2015. For this effective date, Job as Null. But we have to populate it with the job column value from the less effective dates job column value for the same id. That is Job should be populated with "CLERK" in the output.

    Thank you.
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Venu,

    Try this.

    Code (SQL):
    SELECT  DISTINCT id,  MAX(effective_date)effective_date,
    (SELECT MAX(name)name FROM xx_table_name b WHERE b.id = a.id AND B.NAME IS NOT NULL AND rownum<2) name,
    (SELECT MAX(JOB)JOB FROM xx_table_name b WHERE b.id = a.id AND B.JOB IS NOT NULL AND rownum<2) job,
    (SELECT MAX(loc)grade FROM xx_table_name b WHERE b.id = a.id AND B.LOC IS NOT NULL AND rownum<2) loc,
    (SELECT MAX(grade)grade FROM xx_table_name b WHERE b.id = a.id AND B.GRADE IS NOT NULL AND rownum<2) grade,
    (SELECT MAX(basic)basic FROM xx_table_name b WHERE b.id = a.id AND B.BASIC IS NOT NULL AND rownum<2) basic

    FROM xx_table_name a GROUP BY id
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    What this means is that you have a very bad table design and the architect should be hit about the head and shoulders with a large stick.

    Some form of checking for each column would have to be implemented. Again, some variant of Bharat's example should work. However. if I were forced to work with a similar setup, I'd likely start with my SQL example and create a function that would only be called when the most recent column value was NULL. The function, when passed an ID and column name would locate the most recent non-NULL value in the table for that column and return it. It would probably be faster than the example provided, but maybe not.
     
  8. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Since you are GROUPING BY the id column, there is no need for the DISTINCT keyword. The aggregation will only return distinct values of 'ID' by definition.