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(2),
  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'),
(1062, 'CE', '2022-04-01', '2020-03-31', 'NewLicense');

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

1062
CE
2022-04-01
2020-03-31
NewLicense


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,
       p4.CE  as LicenseCE,
       p4.CE2 as Valid_tillCE,
       p4.CE3 as Valid_fromCE,
       p4.CE4 as DescriptionCE
from cte
pivot (max(cte.License1)           for cte.License1 in ([A],  [B],  [C] , [CE])) p1
pivot (max(p1.Valid_till)          for p1.License2  in ([A2], [B2], [C2], [CE2])) p2
pivot (max(p2.Valid_from)          for p2.License3  in ([A3], [B3], [C3], [CE3])) p3
pivot (max(p3.Details_description) for p3.License4  in ([A4], [B4], [C4], [CE4])) p4;


persnr
lastchecked
ID_Card
LicenseA
Valid_tillA
Valid_fromA
DescriptionA
LicenseB
Valid_tillB
Valid_fromB
DescriptionB
LicenseC
Valid_tillC
Valid_fromC
DescriptionC
LicenseCE
Valid_tillCE
Valid_fromCE
DescriptionCE


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

00123
2020-10-21
1061
null
null
null
null
B
2025-02-19
2018-04-01
79064
null
null
null
null
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
null
null
null
null

00124
2020-10-21
1062
null
null
null
null
null
null
null
null
null
null
null
null
CE
2022-04-01
2020-03-31
NewLicense

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


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,
       max(p4.A )  as LicenseA,
       max(p4.A2)  as Valid_tillA,
       max(p4.A3)  as Valid_fromA,
       max(p4.A4)  as DescriptionA,
       max(p4.B )  as LicenseB,
       max(p4.B2)  as Valid_tillB,
       max(p4.B3)  as Valid_fromB,
       max(p4.B4)  as DescriptionB,
       max(p4.C )  as LicenseC,
       max(p4.C2)  as Valid_tillC,
       max(p4.C3)  as Valid_fromC,
       max(p4.C4)  as DescriptionC,
       max(p4.CE ) as LicenseCE,
       max(p4.CE2) as Valid_tillCE,
       max(p4.CE3) as Valid_fromCE,
       max(p4.CE4) as DescriptionCE
from cte
pivot (max(cte.License1)           for cte.License1 in ([A],  [B],  [C] , [CE])) p1
pivot (max(p1.Valid_till)          for p1.License2  in ([A2], [B2], [C2], [CE2])) p2
pivot (max(p2.Valid_from)          for p2.License3  in ([A3], [B3], [C3], [CE3])) p3
pivot (max(p3.Details_description) for p3.License4  in ([A4], [B4], [C4], [CE4])) p4
group by p4.persnr,
         p4.lastchecked,
         p4.ID_Card;


persnr
lastchecked
ID_Card
LicenseA
Valid_tillA
Valid_fromA
DescriptionA
LicenseB
Valid_tillB
Valid_fromB
DescriptionB
LicenseC
Valid_tillC
Valid_fromC
DescriptionC
LicenseCE
Valid_tillCE
Valid_fromCE
DescriptionCE


00123
2020-10-21
1061
A
2022-01-01
2018-01-01
95389
B
2025-02-19
2018-04-01
79064
C
2024-03-16
2018-09-11
84532
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
CE
2022-04-01
2020-03-31
NewLicense


Warning: Null value is eliminated by an aggregate or other SET operation.

persnr	lastchecked	ID_Card	LicenseA	Valid_tillA	Valid_fromA	DescriptionA	LicenseB	Valid_tillB	Valid_fromB	DescriptionB	LicenseC	Valid_tillC	Valid_fromC	DescriptionC	LicenseCE	Valid_tillCE	Valid_fromCE	DescriptionCE
00123	2020-10-21	1061	null	null	null	null	null	null	null	null	C	2024-03-16	2018-09-11	84532	null	null	null	null
00123	2020-10-21	1061	null	null	null	null	B	2025-02-19	2018-04-01	79064	null	null	null	null	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	null	null	null	null
00124	2020-10-21	1062	null	null	null	null	null	null	null	null	null	null	null	null	CE	2022-04-01	2020-03-31	NewLicense
00124	2020-10-21	1062	A	2021-03-15	2019-11-11	83499	null	null	null	null	null	null	null	null	null	null	null	null