This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,899 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Concatenate specific rows with analytical function/plsql

Sandy0594
Sandy0594 Member Posts: 680 Bronze Badge
edited Aug 8, 2019 4:12PM in SQL & PL/SQL

Hi All,

I have been asked this in an Interview, wondering how I could achieve the desired output.

I am using Oracle Version: 11.2.0.4.0 but I am interested in solutions with higher oracle version as well.

CREATE TABLE xxc_temp (request_id NUMBER);Insert into XXC_TEMP (REQUEST_ID) values (475607);Insert into XXC_TEMP (REQUEST_ID) values (475613);Insert into XXC_TEMP (REQUEST_ID) values (475617);Insert into XXC_TEMP (REQUEST_ID) values (475618);Insert into XXC_TEMP (REQUEST_ID) values (475620);Insert into XXC_TEMP (REQUEST_ID) values (475622);Insert into XXC_TEMP (REQUEST_ID) values (475623);Insert into XXC_TEMP (REQUEST_ID) values (475627);Insert into XXC_TEMP (REQUEST_ID) values (475628);Insert into XXC_TEMP (REQUEST_ID) values (475629);Insert into XXC_TEMP (REQUEST_ID) values (475631);Insert into XXC_TEMP (REQUEST_ID) values (475636);Insert into XXC_TEMP (REQUEST_ID) values (475637);Insert into XXC_TEMP (REQUEST_ID) values (475642);Insert into XXC_TEMP (REQUEST_ID) values (475649);Insert into XXC_TEMP (REQUEST_ID) values (475650);Insert into XXC_TEMP (REQUEST_ID) values (475635);Insert into XXC_TEMP (REQUEST_ID) values (475651);Insert into XXC_TEMP (REQUEST_ID) values (475657);Insert into XXC_TEMP (REQUEST_ID) values (475663);Insert into XXC_TEMP (REQUEST_ID) values (475664);Insert into XXC_TEMP (REQUEST_ID) values (475665);Insert into XXC_TEMP (REQUEST_ID) values (475678);Insert into XXC_TEMP (REQUEST_ID) values (475675);Insert into XXC_TEMP (REQUEST_ID) values (475679);Insert into XXC_TEMP (REQUEST_ID) values (475680);Insert into XXC_TEMP (REQUEST_ID) values (475688);Insert into XXC_TEMP (REQUEST_ID) values (475697);SELECT request_idFROM    xxc_temp;

pastedImage_0.png

Every 5 records(request_id) from the above table needs to be concatenated with comma delimiter which should look something like below

Desired Output:

475607,475613,475617,475618,475620

475622,475623,475627,475628,475629

475631,475636,475637,475642,475649

..

..

475680,475688,475697

I've tried using function NTILE but the output would be in a different format

select val_ntile,listagg(request_id,',')within group(order by val_ntile) formatted_stringfrom(select request_id,val_ntilefrom   (select request_id,        ntile(6) over(order by request_id)as val_ntile        from  xxc_temp                   )where 1=1)group by val_ntile;

pastedImage_3.png

Any suggestions are appreciated. PL/SQL approach is fine too.

Thanks!

Tagged:
Sandy0594L. Fernigrini

Best Answer

  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
    edited Aug 8, 2019 3:23PM Answer ✓

    This does it on two steps:

    WITH vSource AS (

        SELECT REQUEST_ID, TRUNC( (ROW_NUMBER() OVER (ORDER BY REQUEST_ID)  - 1) /5) AS VirtGroup

        FROM XXC_TEMP)

    SELECT LISTAGG(REQUEST_ID,',') FROM vSource

    GROUP BY VirtGroup;

Answers

  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
    edited Aug 8, 2019 3:23PM Answer ✓

    This does it on two steps:

    WITH vSource AS (

        SELECT REQUEST_ID, TRUNC( (ROW_NUMBER() OVER (ORDER BY REQUEST_ID)  - 1) /5) AS VirtGroup

        FROM XXC_TEMP)

    SELECT LISTAGG(REQUEST_ID,',') FROM vSource

    GROUP BY VirtGroup;

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 8, 2019 3:41PM

    Hi,

    L. Fernigrini wrote:This does it on two steps:WITH vSource AS ( SELECT REQUEST_ID, TRUNC( (ROW_NUMBER() OVER (ORDER BY REQUEST_ID) - 1) /5) AS VirtGroup FROM XXC_TEMP)SELECT LISTAGG(REQUEST_ID,',') FROM vSourceGROUP BY VirtGroup;

    OP was considerate enough to post error-free CREATE TABLE and INSERT statements.  We should be considerate enough to test our replies.

    I think you meant something like this:

    WITH    vSource    AS(    SELECT  request_id    ,       ROW_NUMBER () OVER (ORDER BY  request_id)  AS rn    FROM    xxc_temp)SELECT    LISTAGG (request_id, ',') WITHIN GROUP (ORDER BY rn)  AS strFROM      vSourceGROUP BY  CEIL (rn / 5)ORDER BY  CEIL (rn / 5);

    I tested this in Oracle 12.2, but it should work just as well in 11.2.

    Sandy0594L. FernigriniSandy0594
  • Sandy0594
    Sandy0594 Member Posts: 680 Bronze Badge
    edited Aug 8, 2019 3:44PM

    Good catch Frank

  • L. Fernigrini
    L. Fernigrini Database Practice Lead Rosario, ArgentinaMember Posts: 4,196 Gold Crown
    edited Aug 8, 2019 4:00PM

    You are right (as always ) I did not pay attention to the order inside each concatenation group, nor the ordering of the entire set. So calculating the group has to be done on the main query.

    And your CEIL(rn / 5) is simpler thatn TRUNC( (Ord -1) / 5).

    I arrived (late) to the almost same script as yours while fixing it:

    WITH vSource AS (

        SELECT REQUEST_ID,  ROW_NUMBER() OVER (ORDER BY REQUEST_ID) AS Ord

        FROM XXC_TEMP)

    SELECT LISTAGG(REQUEST_ID,',') WITHIN GROUP (ORDER BY Ord)

    FROM vSource

    GROUP BY TRUNC((Ord - 1) / 5)

    ORDER BY TRUNC((Ord - 1) / 5)

  • Frank Kulash
    Frank Kulash Boston, USAMember, Moderator Posts: 43,002 Red Diamond
    edited Aug 8, 2019 4:12PM

    Hi,

    L. Fernigrini wrote:... So calculating the group has to be done on the main query.  ...

    Oh, not necessarily:

    WITH vSource AS (    SELECT REQUEST_ID, TRUNC( (ROW_NUMBER() OVER (ORDER BY REQUEST_ID)  - 1) /5) AS VirtGroup    FROM XXC_TEMP)SELECT LISTAGG(REQUEST_ID,',') WITHIN GROUP (ORDER BY request_id) AS strFROM vSourceGROUP BY VirtGroupORDER BY VirtGroup;

    I'm not saying this is better than the queries in replies #2 and #4; I'm just saying there's nothing wrong with deriving the group in the sub-query.

    L. Fernigrini