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_id
FROM 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_string
from(
select request_id,val_ntile
from (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!