您需要枚举行并进行聚合。在mysql（v8之前）中，它看起来像：

select address, ssn,
       max(case when rn = 1 then id end) as id1,
       max(case when rn = 1 then type end) as type1,
       max(case when rn = 1 then date end) as date1,
       max(case when rn = 2 then id end) as id2,
       max(case when rn = 2 then type end) as type2,
       max(case when rn = 2 then date end) as date2
       . . .
from (select c.*,
             (@rn := if(@as = concat_ws(':', address, ssn), @rn + 1,
                        if(@as := concat_ws(':', address, ssn), 1, 1)
                       )
             ) as rn
      from (select c.* from customers c order by address, ssn, date desc) c cross join
           (select @as := '', @rn := 0) params
     ) c
group by address, ssn;

请注意，这不会重复 address 以及 ssn . 这似乎没什么用，但你当然可以在每组中重复这些列。

地址复制的次数有限制吗？如果有一个已知的限制，那么每个副本都可以有多个左连接。如果您知道只有6个或更少的副本，那么下面的解决方案就是：

with a as (
select 
    ID
    ,type
    ,date
    ,address
    ,SSN
    row_number() over(partition by address, SSN order by date desc) as R
from Customers
)

select 
    a.id ID1
    ,a.type TYPE1
    ,a.date DATE1
    ,a.address ADDRESS1
    ,a.ssn SSN1

    ,b.id ID2
    ,b.type TYPE2
    ,b.date DATE2
    ,b.address ADDRESS2
    ,b.ssn SSN2

    ,c.id ID3
    ,c.type TYPE3
    ,c.date DATE3
    ,c.address ADDRESS3
    ,c.ssn SSN3

    ,d.id ID4
    ,d.type TYPE4
    ,d.date DATE4
    ,d.address ADDRESS4
    ,d.ssn SSN4

    ,e.id ID5
    ,e.type TYPE5
    ,e.date DATE5
    ,e.address ADDRESS5
    ,e.ssn SSN5

    ,f.id ID6
    ,f.type TYPE6
    ,f.date DATE6
    ,f.address ADDRESS6
    ,f.ssn SSN6

from a
left join
    (select * from a
    where r=2
    ) b
on a.address=b.address and a.ssn=b.ssn

left join
    (select * from a
    where r=3
    ) c
on a.address=c.address and a.ssn=c.ssn

left join
    (select * from a
    where r=4
    ) d
on a.address=d.address and a.ssn=d.ssn

left join
    (select * from a
    where r=5
    ) e
on a.address=e.address and a.ssn=e.ssn

left join
    (select * from a
    where r=6
    ) f
on a.address=f.address and a.ssn=f.ssn

where r=1

如果有6个以上的列，只需将另一组列添加到select语句：

,f.id ID6
    ,f.type TYPE6
    ,f.date DATE6
    ,f.address ADDRESS6
    ,f.ssn SSN6

以及from语句的新左联接：

left join
    (select * from a
    where r=6
    ) f
on a.address=f.address and a.ssn=f.ssn