Home » Fusion Middleware & Colab Suite » Business Intelligence » group every 5 records
group every 5 records [message #686863] Fri, 27 January 2023 10:15 Go to next message
deahayes
Messages: 6
Registered: January 2023
Junior Member
hello all,
Im looking to create a sql query where I can group every 5 rows. I plan to use this on a BI publisher template where it should only show 5 records per template.
I am thinking query should look like this: any suggestions on how to create it.
grpid project id
1 A 111
1 B 123
1 D 223
1 C 212
1 E 345
2 F 667
2 A 55
2 B 44
2 D 33
2 C 3322222
3 E 33
3 F 988
Re: group every 5 records [message #686867 is a reply to message #686863] Fri, 27 January 2023 11:12 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I am not familiar with BI, so I don't know if there is some built-in method or not, so this is just responding to the question about the SQL query to provide a group id. It does not look like your data is in any particular order and you did not provide create and insert statements for sample data, so the following is just a brief simulation of one method, using Oracle demo data. There may also be newer better methods.

SCOTT@orcl_12.1.0.2.0> column project format a7
SCOTT@orcl_12.1.0.2.0> select ceil(rownum/5) grpid, substr(job,1,1) project, empno id
  2  from   emp
  3  /

     GRPID PROJECT         ID
---------- ------- ----------
         1 C             7369
         1 S             7499
         1 S             7521
         1 M             7566
         1 S             7654
         2 M             7698
         2 M             7782
         2 A             7788
         2 P             7839
         2 S             7844
         3 C             7876
         3 C             7900
         3 A             7902
         3 C             7934

14 rows selected.
Re: group every 5 records [message #686871 is a reply to message #686867] Fri, 27 January 2023 13:00 Go to previous message
deahayes
Messages: 6
Registered: January 2023
Junior Member
Thanks. This works select ceil(rownum/5). Data is not in any particular order, just needed to group every 5 records for the report.Thanks again.
Previous Topic: BI Pub out of support
Next Topic: BI Publisher RTF Template Builder
Goto Forum:
  


Current Time: Thu Mar 28 19:21:02 CDT 2024