Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Concatenate specific rows with analytical function/plsql

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!
Best 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
-
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;
-
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.
-
Good catch Frank
-
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)
-
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.