How to display selected result in one row?

Question

I have tables like this:

TABLE 1 - PERSON:

m_id | name  |
-------------
22   | jo    |
-------------
77   | john  |
--------------

TABLE 2 - AMT_DATA

m_id | amt   | activity  |
-------------------------
22   | 100   |    -      |
-------------------------
77   | 300   |    n      |
-------------------------

TABLE 3 - STATUS_DATA:

m_id  |  status     |  s_date         |

---

 22   |    first    |    01.01.2000 |

---

 22   |    second   |    01.01.2001 |

---

 22   |    third    |    01.01.2002 |

---

 77   |    first    |    01.01.2001 |

---

 77   |    third    |    01.01.2002 |

---

For one m_id there can be more rows in table STATUS_DATA.

I have this select but it will return multiple rows for one m_id.

SELECT PERSON.M_ID, PERSON.NAME, AMT_DATA.AMT, AMT_DATA.ACTIVITY, STATUS_DATA.STATUS, STATUS_DATA.S_DATE FROM PERSON 
INNER JOIN AMT_DATA.M_ID ON AMT_DATA.M_ID = PERSON.M_ID
INNER JOIN STATUS_DATA.M_ID ON STATUS_DATA.M_ID = PERSON.M_ID
WHERE (AMT_DATA.ACTIVITY = '-');

Can i somehow get result for one m_id in one row? Like this:

person_id  | person_name | amt_data.amt | amt_data.activity | status_data.status1 | status_data.s_date1 | .... |status_data.statusN | status_data.s_dateN

Thank you

Answer 1

A stored function, which returns SYS_REFCURSOR type variable, can be created in order to produce the sql statement that includes conditional aggregation such as

CREATE OR REPLACE FUNCTION Get_Person_Status RETURN SYS_REFCURSOR IS
  v_recordset SYS_REFCURSOR;
  v_sql       VARCHAR2(32767);
  v_cols      VARCHAR2(32767);  
BEGIN

  SELECT LISTAGG( 'MAX(CASE WHEN rn = '''||rn||''' THEN '||status||' END) AS status'||rn||,
                  'MAX(CASE WHEN rn = '''||rn||''' THEN '||s_date||' END) AS s_date'||rn  ',' )
          WITHIN GROUP ( ORDER BY rn )
    INTO v_cols
    FROM (SELECT ROW_NUMBER() OVER (PARTITION BY m_id ORDER BY s_date) AS rn, s.*
            FROM status_data s);

  v_sql := 'SELECT p.m_id AS person_id,
                   p.name AS person_name,
                   a.amt,
                   a.activity,
                   '|| v_cols ||'
              FROM person p 
              JOIN amt_data a
                ON a.m_id = p.m_id
              JOIN status_data s
                ON s.m_id = p.m_id
             GROUP BY p.m_id, p.name, a.amt, a.activity 
             ORDER BY p.m_id'; 

  OPEN v_recordset FOR v_sql;
  RETURN v_recordset;
END;
/

and then call by using

VAR rc REFCURSOR
EXEC :rc := Get_Person_Status;
PRINT rc

from SQL developer's console to produce columns dynamically depending on the status and s_date values varying throughout the rows.