Values across multiple rows brought into one row (separate cells) if rows have matching IDs. NULL values if ID only exists once

Question

Relatively new to Window Functions but I'm pretty sure that's what is needed here. I'm trying to combine two or more rows returned in a dataset that share the same ID/dense_rank into one row.

If there are IDs in the dataset that do not have matches within that table, there will be NULL values.

See what I would like the final results to look like and what I am currently receiving. Not sure if it would be row_number or dense_rank. Thanks! (query below).

select 
DENSE_RANK() OVER(ORDER BY l.unit_key) AS ID,
l.unit_key, 
l.start_date,  
u.area, 
c.amount 

from unit_rt u
    join lease_rt l on u.unit_key = l.unit_key
    join charges_rt c on l.lease_key = c.lease_key

Answer 1

If I follow you correctly, you can join and rank records having the same unit_key by ascending start_date, and then pivot the resultset with conditional aggregation:

select 
    unit_key,
    max(case when rn = 1 then l.start_date end) prior_lease_date,
    max(case when rn = 1 then u.area       end) prior_area,
    max(case when rn = 1 then c.amount     end) prior_amount,
    max(case when rn = 2 then l.start_date end) new_lease_date,
    max(case when rn = 2 then u.area       end) new_area,
    max(case when rn = 2 then c.amount     end) new_amount
from unit_rt u
inner join (
    select 
        l.*, 
        row_number() over(partition by l.unit_key order by l.start_date) rn
    from lease_rt l
) l on u.unit_key  = l.unit_key
inner join charges_rt c on l.lease_key = c.lease_key
group by l.unit_key

Note that this assumes either one or two records per unit_key only, as shown in your data.