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!

Manipulating Select SQL

Discussion in 'SQL PL/SQL' started by rudolf_r, Mar 31, 2014.

  1. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,

    I need a select statement with data manipulation where for example I select all the last_names, and first_names of employees with the same last_names and then have the values seperated by commas in the same column. For example the output should look like this,

    Department_id Last_names First_names
    ============ ========= =========
    10
     
  2. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hi All,

    My apologies, I have not completed the previous post.

    I need a select statement with data manipulation where for example I select all the last_names,
    and first_names of employees with the same department_id,
    and than have the values seperated by commas in the same column.
    For example the output should look like this,

    Department_id Last_names First_names
    ============ =========== ============
    10 King, Taylor Steven, John

    30 Tobias, Baida, Elias, Ewin, Ann
    Khoo

    Thank you.
    Rudolf
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    There is a lot of examples of aggregation of lines.

    General option (>= 10g) :

    Code (SQL):


    SELECT
             deptno ,  
             xmlagg     ( xmltype( '<V>' || ename || '</V>' )       ORDER BY VAL DESC     ).getStringVal()      AS employees
     FROM   emp GROUP BY deptno;      


    SQL>
    DEPTNO EMPLOYEES
    ---------- --------------------------------------------------        
    10 CLARK,KING,MILLER         20 ADAMS,FORD,JONES,SCOTT,SMITH        
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


     

    For 11G

    Code (SQL):


    COLUMN employees FORMAT A50  
    SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
    FROM   emp GROUP BY deptno;      


    SQL>
    DEPTNO EMPLOYEES
    ---------- --------------------------------------------------        
    10 CLARK,KING,MILLER         20 ADAMS,FORD,JONES,SCOTT,SMITH        
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

     
    See link :http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Possibly a better example showing how to make both last and first name lists in the same query in 11.2:


    Code (SQL):

    SQL> CREATE TABLE employees(
      2          empid   NUMBER NOT NULL,
      3          lastname        varchar2(40) NOT NULL,
      4          firstname       varchar2(40) NOT NULL,
      5          deptid  NUMBER NOT NULL,
      6          mgrid   NUMBER);


    TABLE created.


    SQL>
    SQL>
    SQL> CREATE SEQUENCE empidseq START WITH 1 INCREMENT BY 1 nocycle nomaxvalue ORDER;


    SEQUENCE created.


    SQL>
    SQL> INSERT ALL
      2  INTO employees
      3  VALUES(empidseq.NEXTVAL, 'Boosenschniefer','Ingrid', 100, NULL)
      4  INTO employees
      5  VALUES(empidseq.NEXTVAL, 'Grappenhaffler','Boris', 100, 1)
      6  INTO employees
      7  VALUES(empidseq.NEXTVAL, 'Hoogengriefer','Almonso', 120, 5)
      8  INTO employees
      9  VALUES(empidseq.NEXTVAL, 'Jargenpfist','Ingrid', 120, 5)
     10  INTO employees
     11  VALUES(empidseq.NEXTVAL, 'Framperchisel','Leone', 120, NULL)
     12  INTO employees
     13  VALUES(empidseq.NEXTVAL, 'Eutha','Paulette', 130, 8)
     14  INTO employees
     15  VALUES(empidseq.NEXTVAL, 'Quackenfonse','Tiberia', 100, 1)
     16  INTO employees
     17  VALUES(empidseq.NEXTVAL, 'Ilmaheaver','Yuda', 130, NULL)
     18  INTO employees
     19  VALUES(empidseq.NEXTVAL, 'Krakkenheimer','Wilma', 130, 8)
     20  INTO employees
     21  VALUES(empidseq.NEXTVAL, 'Orkenflorker','Rapunzel', 130, 8)
     22  INTO employees
     23  VALUES(empidseq.NEXTVAL, 'Rolfabrinkle','Ophelia', 100, 1)
     24  SELECT * FROM dual;


    11 ROWS created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> SELECT deptid, listagg(lastname, ',') WITHIN GROUP (ORDER BY lastname) AS last_name,
      2                 listagg(firstname,',') WITHIN GROUP (ORDER BY lastname) AS first_name
      3  FROM employees
      4  GROUP BY deptid;


        DEPTID LAST_NAME                                                                        FIRST_NAME
    ---------- -------------------------------------------------------------------------------- --------------------------------------------------
           100 Boosenschniefer,Grappenhaffler,Quackenfonse,Rolfabrinkle                         Ingrid,Boris,Tiberia,Ophelia
           120 Framperchisel,Hoogengriefer,Jargenpfist                                          Leone,Almonso,Ingrid
           130 Eutha,Ilmaheaver,Krakkenheimer,Orkenflorker                                      Paulette,Yuda,Wilma,Rapunzel


    SQL>
     

    It is possible in 11g to use listagg() more than once in the same query. The same is true for xmlagg() in 10g.
     
  5. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hi,

    Thanks for your response and suggestions. I have tried the following as per your suggestion:

    SELECT DISTINCT doc_id,
    LISTAGG(surname, ',') WITHIN GROUP (ORDER BY surname) AS SURNAME,
    LISTAGG(first_name, ',') WITHIN GROUP (ORDER BY first_name) AS FIRST_NAME
    FROM V_TABLE_VIEW
    GROUP BY doc_id;

    The resulted output is:

    DOC_ID SURNAME FIRST_NAMES
    ======== ================== ================
    B02211/2012 LINT,LINT MALAN, PAUL

    T0011/2010 VAN WYK, VAN WYK JOHAN, EVELINE

    I actually need the Surnames to only be listed once IF they are the same and listed if NOT, the FIRST_NAMES are correct.

    Thanks
    Rudolf
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Since the 'duplicate' rows are not actually duplicates you won't be able to do that in a simple SQL query:


    Code (SQL):

    SQL> SELECT deptid, listagg(lastname, ',') WITHIN GROUP (ORDER BY lastname) AS last_name,
      2                 listagg(firstname,',') WITHIN GROUP (ORDER BY lastname) AS first_name
      3  FROM employees
      4  GROUP BY deptid;


        DEPTID LAST_NAME                                                                        FIRST_NAME
    ---------- -------------------------------------------------------------------------------- --------------------------------------------------
           100 Boosenschniefer,Grappenhaffler,Quackenfonse,Rolfabrinkle                         Ingrid,Boris,Tiberia,Ophelia
           120 Framperchisel,Hoogengriefer,Jargenpfist,Jargenpfist                              Leone,Almonso,Ingrid,Ingride
           130 Eutha,Ilmaheaver,Krakkenheimer,Orkenflorker                                      Paulette,Yuda,Wilma,Rapunzel


    SQL>
    SQL> SELECT deptid, listagg(lastname, ',') WITHIN GROUP (ORDER BY lastname) AS last_name,
      2                 listagg(firstname,',') WITHIN GROUP (ORDER BY lastname) AS first_name
      3  FROM (SELECT DISTINCT deptid, lastname, firstname FROM employees)
      4  GROUP BY deptid;


        DEPTID LAST_NAME                                                                        FIRST_NAME
    ---------- -------------------------------------------------------------------------------- --------------------------------------------------
           100 Boosenschniefer,Grappenhaffler,Quackenfonse,Rolfabrinkle                         Ingrid,Boris,Tiberia,Ophelia
           120 Framperchisel,Hoogengriefer,Jargenpfist,Jargenpfist                              Leone,Almonso,Ingrid,Ingride
           130 Eutha,Ilmaheaver,Krakkenheimer,Orkenflorker                                      Paulette,Yuda,Wilma,Rapunzel


    SQL>
     

    DISTINCT is not an option to listagg:


    Code (SQL):

    SQL> SELECT deptid, listagg(DISTINCT lastname, ',') WITHIN GROUP (ORDER BY lastname) AS last_name,
      2                 listagg(firstname,',') WITHIN GROUP (ORDER BY lastname) AS first_name
      3  FROM employees
      4  GROUP BY deptid;
    SELECT deptid, listagg(DISTINCT lastname, ',') WITHIN GROUP (ORDER BY lastname) AS last_name,
                   *
    ERROR at line 1:
    ORA-30482: DISTINCT OPTION NOT allowed FOR this FUNCTION

    SQL>
     

    You will need to write a PL/SQL script to generate the output you want given your data.
     
  7. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hi David,

    My PL/SQL skills are a bit limited for this. I would appreciate any suggestions on how to do this or even pay someone to do this for me.

    Thanks
    Rudolf
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.

    "I actually need the Surnames to only be listed once IF they are the same and listed if NOT, the FIRST_NAMES are correct."

    Note : If you have no scheme HR, can use tables which were provided by David, but then modify this sql-query.

    One of versions of decisions:

    Code (SQL):


    SELECT
    listagg(first_name,',') WITHIN GROUP (ORDER BY department_id) AS firstname,
    listagg(last_name,',') WITHIN GROUP (ORDER BY department_id) AS lastname
    FROM (
    SELECT
    department_id,
    last_name ,
    listagg(first_name,',') WITHIN GROUP (ORDER BY employee_id) AS first_name
    FROM hr.employees
    GROUP BY last_name, department_id
    )
    GROUP BY department_id ;

    SQL>
     
     
  9. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hi Sergey, Thanks for the sql. The output is actually not what I were looking for. My select when I run it produces the correct data but I need to further group and list the values for SURNAMES, FIRST_NAMES, etc.

    For my first example see Book1.xls attached.

    I would actually need to have the data sorted like in Book2.xls

    Thanks.
    Rudolf
     

    Attached Files:

  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Oracle version ?
     
  11. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Oracle 11g
     
  12. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Oracle 11g
     
  13. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Option 1

    Note :

    Code (SQL):



    WITH
    your_data
    AS(
    SELECT 'T00040/2012' DOC_NR,'KING' SURNAME,'STEVEN' FIRST_NAMES,'RICH'    SURNAME_2, 'ASTRID' FIRST_NAMES_2  FROM dual UNION ALL
    SELECT 'T00040/2012','KING','JANETE','RICH','ASTRID'  FROM dual UNION ALL
    SELECT 'T00041/2012','KING','JANETE','RIEDEL','HAGEN'  FROM dual UNION ALL
    SELECT 'T00041/2012',    'KING',    'STEVEN', 'RIEDEL','HAGEN'  FROM dual UNION ALL
    SELECT 'T00183/2012','TAYLOR','GERTRUIDA','RUBEL','JACQUES'  FROM dual UNION ALL
    SELECT 'T00183/2012','TAYLOR','GERTRUIDA','RUBEL','NADINE'  FROM dual UNION ALL
    SELECT 'T00183/2012','TAYLOR','JOHAN','RUBEL','JACQUES'   FROM dual UNION ALL
    SELECT 'T00183/2012','TAYLOR','JOHAN','RUBEL','NADINE'  FROM dual
    )
    ,preres AS
    (
    SELECT  
    y.doc_nr,y.surname,
    CAST (collect(DISTINCT first_names) AS sys.odcivarchar2list)  zz,
    y.surname_2,
    listagg(first_names_2,',') WITHIN GROUP (ORDER BY NULL) AS first_names_2,
    CAST (collect(DISTINCT first_names_2) AS sys.odcivarchar2list)  zz1
    FROM your_data y
    GROUP BY y.doc_nr,y.surname,y.surname_2
    )

    SELECT
          pr.doc_nr,pr.surname,
           (SELECT  
                 listagg(column_value,',') WITHIN GROUP (ORDER BY rownum)
            FROM TABLE(pr.zz)
           ) AS first_names ,
           
           pr.surname_2,
           (SELECT  
                 listagg(column_value,',') WITHIN GROUP (ORDER BY rownum)
            FROM TABLE(pr.zz1)
           ) AS first_names_2
           
    FROM preres pr;

    SQL>

    DOC_NR      SURNAME FIRST_NAMES                                SURNAME_2 FIRST_NAMES_2
    ----------- ------- ----------------------------------------- --------- -------------------------------------------------------------------
    T00040/2012 KING    JANETE,STEVEN                              RICH      ASTRID
    T00041/2012 KING    JANETE,STEVEN                              RIEDEL    HAGEN
    T00183/2012 TAYLOR  GERTRUIDA,JOHAN                            RUBEL     JACQUES,NADINE




     



    Option 2

    Code (SQL):


    WITH
    your_data
    AS(
    SELECT 'T00040/2012' DOC_NR,'KING' SURNAME,'STEVEN' FIRST_NAMES,'RICH'    SURNAME_2, 'ASTRID' FIRST_NAMES_2  FROM dual UNION ALL
    SELECT 'T00040/2012','KING','JANETE','RICH','ASTRID'  FROM dual UNION ALL
    SELECT 'T00041/2012','KING','JANETE','RIEDEL','HAGEN '  FROM dual UNION ALL
    SELECT 'T00041/2012',    'KING',    'STEVEN', 'RIEDEL','HAGEN'  FROM dual UNION ALL
    SELECT 'T00183/2012','TAYLOR','GERTRUIDA','RUBEL','JACQUES'  FROM dual UNION ALL
    SELECT 'T00183/2012','TAYLOR','GERTRUIDA','RUBEL','NADINE'  FROM dual UNION ALL
    SELECT 'T00183/2012','TAYLOR','JOHAN','RUBEL','JACQUES'   FROM dual UNION ALL
    SELECT 'T00183/2012','TAYLOR','JOHAN','RUBEL','NADINE'  FROM dual
    )
    ,
    preres AS
    (
    SELECT
        y.*,
        NULLIF(y.first_names,lead(y.first_names) OVER (partition BY y.doc_nr,y.surname ORDER BY first_names )) dv_names1,
        NULLIF(y.first_names_2,lead(y.first_names_2) OVER (partition BY y.doc_nr,y.surname_2 ORDER BY first_names_2)) dv_names2    
    FROM your_data y
    )
    SELECT
          pr.doc_nr,pr.surname,
          listagg(dv_names1,',') WITHIN GROUP (ORDER BY rownum) first_names,
          pr.surname_2,
          listagg(dv_names2 ,',') WITHIN GROUP (ORDER BY rownum) first_names_2
    FROM preres pr
    GROUP BY  doc_nr ,pr.surname,pr.doc_nr,pr.surname_2;

    SQL>

    SQL>
     
    DOC_NR      SURNAME FIRST_NAMES              SURNAME_2 FIRST_NAMES_2
    ----------- ------- ------------------------ --------- -------------------------
    T00040/2012 KING    JANETE,STEVEN            RICH      ASTRID
    T00041/2012 KING    JANETE,STEVEN            RIEDEL    HAGEN
    T00183/2012 TAYLOR  GERTRUIDA,JOHAN       RUBEL     NADINE,JACQUES




     
     
  14. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Thanks, Sergey. I'll look at the sql's during the weekend and will let you know how it is going.
     
  15. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hi Sergey,

    I have looked at this sql code and tested, the results are as expected. The big thing now is to include this now into what I need to do. For example:
    If there are more than one owner the information should be separated by a comma in the same column, eg.
    Moses Thimo Hangula
    Leena Shipena
    would be:

    surname1 first_names
    =============== ==================
    Hangula, Shipena Moses Thimo, Leena
    King Steven, Janete
    (Where surnames are the same, value is only displayed once)

    This might require me to pay someone for this SQL help, which I am willing to do.

    Thanks
    Rudolf
     
  16. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,rudolf_r.

    Hi.
    At a forum here simply help each other : examples of SQL queries, pl/sql examples, offer versions of decisions, etc.

    If It is necessary simple help in writing of some SQL queries , then formulate a question, provide the scripts of create table ,the script of filling these tables and provide desirable result in the form of the table.

    But if there is a speech about development of the ready decision, for example a SQL query for the report and result it is necessary to receive in a certain period (since other operation depends on it), then details can be discussed by mail.
     
  17. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Sergey, thanks for your patience and explainig. It is a bit difficult for me to provide create table scripts, since I'm testing the SQL's for the report from 2 views that were created from joining different tables. Also the tables and views contain some sensitive data.
    The SQL that I'm running is:
    Code (SQL):
    SELECT DISTINCT t.TITLE_DEED,
    t.SURNAME_OF_TRANSFEROR,
    t.FIRST_NAMES_OF_TRANSFEROR,
    t.DATE_OF_BIRTH AS DOB_TRANSFEROR,
    t.MARITAL_DESCRIPTION AS M_STATUS_TRANSFEROR,
    t.ID_NO AS ID_TRANSFEROR,  
    e.SURNAME_OF_TRANSFEREE,
    e.FIRST_NAMES_OF_TRANSFEREE,
    e.DATE_OF_BIRTH AS DOB_TRANSFEREE,
    e.MARITAL_DESCRIPTION AS M_STATUS_TRANSFEREE,
    e.ID_NO AS ID_TRANSFEREE,
    t.REG_DIV_ID AS REG_DIV,
    t.MUNICIPALITY AS TOWN,
    t.SUBURBS_EXTENTIONS AS EXTENTION,
    t.PARCEL_NO AS ERF_NO,
    e.AREA_SIZE_PARCEL AS AREA,
    t.REGISTRATION_DATE AS REG_DATE,
    t.PURCHASE_PRICE AS AMOUNT
    FROM V_TRANSFEROR_VIEW t, V_TRANSFEREE_VIEW e
    WHERE t.DOCUMENTS_ID = e.DOCUMENTS_ID
    AND t.PARCEL_ID = e.PARCEL_ID
    AND UPPER(T.SUBURBS_EXTENTIONS) LIKE ('OLYMPIA')
    AND UPPER(T.MUNICIPALITY) LIKE ('&TOWN_NAME')
    AND UPPER(T.REG_DIV_ID) LIKE ('&REGION')
    AND TRUNC(t.REGISTRATION_DATE) >= TO_DATE('&FROM_DATE')
    AND TRUNC(t.REGISTRATION_DATE) <= TO_DATE('&TO_DATE')
    AND UPPER(E.PARCEL_TYPE) = ('E')
    ORDER BY TITLE_DEED
    Attached is a sample of the result exported into CSV file, see example LAT-Erfs-Sample1.xls . I would like to have the results to look like example LAT-Erfs-Sample2.xls

    Thank you.
    Rudolf
     

    Attached Files:

  18. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Rudolf,
    in the unloading file LAT-Erfs-Sample1.xls all together (all in one column), isn't read
    the file LAT-Erfs-Sample2.xls - all separate columns, everything is good.

    tell, why is used here distinct in your query - really in
    result there is a redundance (repeating lines)?
     
  19. rudolf_r

    rudolf_r Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Sergey, I've uploaded the first sample again. I also picked up the redundant (repeated) lines. There might be duplicated records in the tables. The idea for the query is to get the persons linked to a specific TITLE_DEED in one row, in some cases there are more than one person linked to a TITLE_DEED nr and in most cases their Surnames are the same.
     

    Attached Files:

  20. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Try this :
    Code (SQL):


    WITH
    your_data AS
    (
    SELECT DISTINCT t.TITLE_DEED,
    t.SURNAME_OF_TRANSFEROR,
    t.FIRST_NAMES_OF_TRANSFEROR,
    t.DATE_OF_BIRTH AS DOB_TRANSFEROR,
    t.MARITAL_DESCRIPTION AS M_STATUS_TRANSFEROR,
    t.ID_NO AS ID_TRANSFEROR,  
    e.SURNAME_OF_TRANSFEREE,
    e.FIRST_NAMES_OF_TRANSFEREE,
    e.DATE_OF_BIRTH AS DOB_TRANSFEREE,
    e.MARITAL_DESCRIPTION AS M_STATUS_TRANSFEREE,
    e.ID_NO AS ID_TRANSFEREE,
    t.REG_DIV_ID AS REG_DIV,
    t.MUNICIPALITY AS TOWN,
    t.SUBURBS_EXTENTIONS AS EXTENTION,
    t.PARCEL_NO AS ERF_NO,
    e.AREA_SIZE_PARCEL AS AREA,
    t.REGISTRATION_DATE AS REG_DATE,
    t.PURCHASE_PRICE AS AMOUNT
    FROM V_TRANSFEROR_VIEW t, V_TRANSFEREE_VIEW e
    WHERE t.DOCUMENTS_ID = e.DOCUMENTS_ID
    AND t.PARCEL_ID = e.PARCEL_ID
    AND UPPER(T.SUBURBS_EXTENTIONS) LIKE ('OLYMPIA')
    AND UPPER(T.MUNICIPALITY) LIKE ('&TOWN_NAME')
    AND UPPER(T.REG_DIV_ID) LIKE ('&REGION')
    AND TRUNC(t.REGISTRATION_DATE) >= TO_DATE('&FROM_DATE')
    AND TRUNC(t.REGISTRATION_DATE) <= TO_DATE('&TO_DATE')
    AND UPPER(E.PARCEL_TYPE) = ('E')
    )
    ,
    preres AS
    (
    SELECT
        y.*,
        NULLIF(y.FIRST_NAMES_OF_TRANSFEROR,lead(y.FIRST_NAMES_OF_TRANSFEROR) OVER (partition BY y.TITLE_DEED,y.SURNAME_OF_TRANSFEROR ORDER BY FIRST_NAMES_OF_TRANSFEROR )) dv_fn_transferos,
        NULLIF(y.SURNAME_OF_TRANSFEROR,lead(y.SURNAME_OF_TRANSFEROR) OVER (partition BY y.TITLE_DEED ORDER BY FIRST_NAMES_OF_TRANSFEROR )) dv_sn_transferos,    
        NULLIF(y.FIRST_NAMES_OF_TRANSFEREE,lead(y.FIRST_NAMES_OF_TRANSFEREE) OVER (partition BY y.TITLE_DEED,y.SURNAME_OF_TRANSFEREE ORDER BY FIRST_NAMES_OF_TRANSFEREE )) dv_fn_transferee,
        NULLIF(y.SURNAME_OF_TRANSFEREE,lead(y.SURNAME_OF_TRANSFEREE) OVER (partition BY y.TITLE_DEED ORDER BY FIRST_NAMES_OF_TRANSFEREE )) dv_sn_transferee,
        ROW_NUMBER() OVER (partition BY y.TITLE_DEED ORDER BY efr_no ) rn
    FROM your_data y
    )


    SELECT
          pr.*,
         
     CASE WHEN rn = 1 THEN
    listagg(dv_fn_transferos,',') WITHIN GROUP (ORDER BY rn) OVER (partition BY TITLE_DEED)
    END dv_fn_transferos,
    CASE WHEN rn = 1 THEN
    listagg(dv_sn_transferos,',') WITHIN GROUP (ORDER BY rn) OVER (partition BY TITLE_DEED)
    END dv_sn_transferos,

    CASE WHEN rn = 1 THEN
    listagg(dv_fn_transferee,',') WITHIN GROUP (ORDER BY rn) OVER (partition BY TITLE_DEED)
    END dv_fn_transferee,
    CASE WHEN rn = 1 THEN
    listagg(dv_sn_transferee,',') WITHIN GROUP (ORDER BY rn) OVER (partition BY TITLE_DEED)
    END dv_sn_transferee

    FROM preres  pr;