Concatenate specific rows with analytical function/plsql

Sandy0594

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;

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;

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

Thanks!

L. Fernigrini

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;

L. Fernigrini

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

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.

Sandy0594

Good catch Frank

L. Fernigrini

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

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.