Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2017
    Posts
    2

    Unanswered: Combined column from multiple rows to get a single row

    Hello,

    Need assistance to extract data from multiple rows. Details are:

    Data Stores as below:
    TRAN ID Message Type DATE
    1 pain.009 2017-01-02
    1 pain.012 2017-01-02
    2 pain.010 2017-01-02
    2 pain.012 2017-01-02
    3 pain.011 2017-01-02
    3 pain.012 2017-01-02

    Same table stores request and response. Response will always have message type as 'pain.012', and request can have message type as pain.009, pain.010 or pain.011.

    Requirement is:
    I want to extract all the responses for the day, and need to know what was the type of request for this response.
    Result set like below:

    TRAN ID REQUEST RESPONSE DATE
    1 pain.009 pain.012 2017-01-02
    2 pain.010 pain.012 2017-01-02
    3 pain.011 pain.012 2017-01-02

    Kindly assist and let me know if more details are required?

    Regards,
    Ankur.

  2. #2
    Join Date
    Mar 2007
    Posts
    624
    Hello,

    just do a simple aggregation (GROUP BY query), at least by "TRAN ID". The values of "REQUEST" and "RESPONSE" may be computed as
    Code:
    max( decode( "Message Type", 'pain.012', null, "Message Type" ) ) "REQUEST"
    max( decode( "Message Type", 'pain.012', "Message Type", null ) ) "RESPONSE"
    Or use any other aggregation function than MAX if there is a special rule for obtaining the "REQUEST" when there are multipe rows with the same "TRAN ID" and different "Messsage Type", e.g.
    Code:
    1 pain.009	2017-01-02
    1 pain.011	2017-01-02
    1 pain.012	2017-01-02
    Depending how you want to treat different DATE values for the same "TRAN ID", put it to GROUP BY clause (multiple rows for different "DATE" values) or use the appropriate aggregate function to display the required one (one row for "TRAN ID").
    It is really impossible to deduce the rule from your from your post as all the values for "DATE" are the same.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,095
    Provided Answers: 4
    Here's one option:
    Code:
    SQL> alter session set nls_date_format = 'yyyy-mm-dd';
    
    Session altered.
    
    SQL> select * From test;
    
       TRAN_ID MESSAGE_TYPE         DATUM
    ---------- -------------------- ----------
             1 pain.009             2017-01-02
             1 pain.012             2017-01-02
             2 pain.010             2017-01-02
             2 pain.012             2017-01-02
    
    SQL>   select tran_id,
      2           max (decode (message_type, 'pain.012', null, message_type)) request,
      3           max (decode (message_type, 'pain.012', message_type, null)) response,
      4           datum
      5      from test
      6  group by tran_id, datum;
    
       TRAN_ID REQUEST              RESPONSE             DATUM
    ---------- -------------------- -------------------- ----------
             1 pain.009             pain.012             2017-01-02
             2 pain.010             pain.012             2017-01-02
    
    SQL>
    However, what is the result if response data is different from request date, such as
    Code:
    SQL> update test set datum = date '2017-01-15' where tran_id = 2 and message_type = 'pain.012';
    
    1 row updated.
    
    SQL> select * From test;
    
       TRAN_ID MESSAGE_TYPE         DATUM
    ---------- -------------------- ----------
             1 pain.009             2017-01-02
             1 pain.012             2017-01-02
             2 pain.010             2017-01-02
             2 pain.012             2017-01-15
    
    SQL>   select tran_id,
      2           max (decode (message_type, 'pain.012', null, message_type)) request,
      3           max (decode (message_type, 'pain.012', message_type, null)) response,
      4           datum
      5      from test
      6  group by tran_id, datum;
    
       TRAN_ID REQUEST              RESPONSE             DATUM
    ---------- -------------------- -------------------- ----------
             1 pain.009             pain.012             2017-01-02
             2 pain.010                                  2017-01-02
             2                      pain.012             2017-01-15
    
    SQL>
    Is that OK?

  4. #4
    Join Date
    Jan 2017
    Posts
    2
    Thanks for the feedback guys.

    Please note that date of Response can be different than Request Date.
    Also, I have to extract response for current date only i.e. it should be one of the criteria to select.

    Regards,
    Ankur.

Posting Permissions

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