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 <[email protected]>:
> 
> 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 <[email protected]>:
>> 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: [email protected]
>> To subscribe, unsubscribe, or change list options,
>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>> or email: [email protected]
>> Before posting, please take a moment to review the archives
>> at https://archive.midrange.com/rpg400-l.
>> Please contact [email protected] for any subscription related questions.
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.