Insert a Row after Each Group
【Question】
I have a query in a stored procedure like below:
select x,y from table
And the results will look like below:
x y
1 a
1 b
2 a
2 b
3 a
3 b
I need to add a blank column or zeros when the value of x changes like below:
x y
1 a
1 b
0 0
2 a
2 b
0 0
3 a
3 b
Can this be done in SQL or with BIRT since I’m using the data for BIRT reports?
Below is a reply and the asker’s response:
Sounds like you’re looking for the wrong solution to your problem. Most reporting tools should be able to handle groups of data without having to manipulate the source data in this way. You need to use a group header row or something similar.– TannerJun 9 at 10:45
Thanks, somehow I didn’t think of using group header as a blank row– Charles
【Answer】
It’s inconvenient to insert a row after each group in a result set without in SQL in the absence of reporting tool’s group method. You can use esProc to prepare the data source. The code is simple and easy:
A |
|
1 |
=orac.proc("{call yoursp(?)}",:101:"o":table1) |
2 |
=table1.create().record([0,0]) |
3 |
=table1.group@o(x).conj(~|A2) |
A3’s result:
BIRT can access esProc through JDBC in same way it does to a database. More explanations can be found in How to Call an SPL Script in Java.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL