Re: Suggestion fo SQL

Hi Gio,

I hope I have understood what you wanted - and without trying it on a machine, this is what I thought might work:

with
mm as (
select distinct year, month from your_table
)

select t1.*, t2.*
from mm
left join (
select * from your_table
) as t1
on t1.year = mm.year and t1.month = mm.month and t1.type = 'FT'
left join (
select * from your_table
) as t2
on t2.year = mm.year and t2.month = mm.month and t2.type = 'OR'
order by mm.year, mm.month;

If you want empty "month" lines, you might have to "generate" year/month combinations with a recursive CTE.

HTH
Daniel

Am 21.08.2025 um 19:16 schrieb Daniel Gross <daniel@xxxxxxxx>:

Hi Gio,

you mean, you want an "empty" line, if there is no corresponding line for the same type, year and month?

So there is no record for type OR for 2028/07 - so there should be an empty line?

You should create a CTE with all the months in You table, and another with all the types from your table.

Next cross join this CTEs to get every combination - and then join that against the data from your table.

Right now I'm on the road - but later that evening I could try to do it.

HTH
Daniel

Am 21.08.2025 um 18:40 schrieb gio.cot via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:
Hi All
I have a table like this:
Type Year Month Code value
FT 2028 07 000001 656,22
OR 2028 06 000001 894,01
FT 2025 08 000001 123,45
OR 2025 08 000001 444,45
I would need to get this result with SQL
Type Year Month Code value Type2 Year2
Month 2 Code2 Value2
FT 2028 07 000001 656,22
- - - -
-
- - - -
- OR 2028 06 000001
894,01
FT 2025 08 000001 123,45
OR 2025 08 000001 444,45
How could do that ? (If is possible ??)
Thanks in advance
Gio
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.