SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table cards
(
  persnr varchar(5),
  id int,
  lastchecked date,
  lastcheckedby_user nvarchar(5)
);

insert into cards (persnr, id, lastchecked, lastcheckedby_user) values
('00123', 1061, '2020-10-21', 'me' ),
('00124', 1062, '2020-10-21', 'you');

select * from cards;


persnr
id
lastchecked
lastcheckedby_user


00123
1061
2020-10-21
me

00124
1062
2020-10-21
you


create table details
(
  ID_Card int,
  License nvarchar(1),
  Valid_till date,
  Valid_from date,
  Details_description nvarchar(10)
);

insert into details (ID_Card, License, Valid_till, Valid_from, Details_description) values
(1061, 'A', '2022-01-01', '2018-01-01', '95389'),
(1061, 'B', '2025-02-19', '2018-04-01', '79064'),
(1061, 'C', '2024-03-16', '2018-09-11', '84532'),
(1062, 'A', '2021-03-15', '2019-11-11', '83499');

select * from details;


ID_Card
License
Valid_till
Valid_from
Details_description


1061
A
2022-01-01
2018-01-01
95389

1061
B
2025-02-19
2018-04-01
79064

1061
C
2024-03-16
2018-09-11
84532

1062
A
2021-03-15
2019-11-11
83499


with cte as
(
  select c.persnr,
         c.lastchecked,
         d.ID_Card,
         d.License     as License1, -- license
         d.License+'2' as License2, -- valid_till
         d.License+'3' as License3, -- valid_from
         d.License+'4' as License4, -- details_description
         d.Valid_till,
         d.Valid_from,
         d.Details_description
  from cards c
  join details d
    on d.ID_Card = c.id
)
select p4.persnr,
       p4.lastchecked,
       p4.ID_Card,
       p4.A  as LicenseA,
       p4.A2 as Valid_tillA,
       p4.A3 as Valid_fromA,
       p4.A4 as DescriptionA,
       p4.B  as LicenseB,
       p4.B2 as Valid_tillB,
       p4.B3 as Valid_fromB,
       p4.B4 as DescriptionB,
       p4.C  as LicenseC,
       p4.C2 as Valid_tillC,
       p4.C3 as Valid_fromC,
       p4.C4 as DescriptionC
from cte
pivot (max(cte.License1)           for cte.License1 in ([A],  [B],  [C] )) p1
pivot (max(p1.Valid_till)          for p1.License2  in ([A2], [B2], [C2])) p2
pivot (max(p2.Valid_from)          for p2.License3  in ([A3], [B3], [C3])) p3
pivot (max(p3.Details_description) for p3.License4  in ([A4], [B4], [C4])) p4


persnr
lastchecked
ID_Card
LicenseA
Valid_tillA
Valid_fromA
DescriptionA
LicenseB
Valid_tillB
Valid_fromB
DescriptionB
LicenseC
Valid_tillC
Valid_fromC
DescriptionC


00123
2020-10-21
1061
null
null
null
null
null
null
null
null
C
2024-03-16
2018-09-11
84532

00123
2020-10-21
1061
null
null
null
null
B
2025-02-19
2018-04-01
79064
null
null
null
null

00123
2020-10-21
1061
A
2022-01-01
2018-01-01
95389
null
null
null
null
null
null
null
null

00124
2020-10-21
1062
A
2021-03-15
2019-11-11
83499
null
null
null
null
null
null
null
null

ID_Card	License	Valid_till	Valid_from	Details_description
1061	A	2022-01-01	2018-01-01	95389
1061	B	2025-02-19	2018-04-01	79064
1061	C	2024-03-16	2018-09-11	84532
1062	A	2021-03-15	2019-11-11	83499

persnr	lastchecked	ID_Card	LicenseA	Valid_tillA	Valid_fromA	DescriptionA	LicenseB	Valid_tillB	Valid_fromB	DescriptionB	LicenseC	Valid_tillC	Valid_fromC	DescriptionC
00123	2020-10-21	1061	null	null	null	null	null	null	null	null	C	2024-03-16	2018-09-11	84532
00123	2020-10-21	1061	null	null	null	null	B	2025-02-19	2018-04-01	79064	null	null	null	null
00123	2020-10-21	1061	A	2022-01-01	2018-01-01	95389	null	null	null	null	null	null	null	null
00124	2020-10-21	1062	A	2021-03-15	2019-11-11	83499	null	null	null	null	null	null	null	null