SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table metadata(IdMetadata int, IdMetadataKey int,  Value varchar(50), IdBatch int);
insert into metadata values(1,           3               ,'50550000',                105);
insert into metadata values(2,           2               ,'ABC',                     105);
insert into metadata values(3,           5               ,'Buks',                    105);
insert into metadata values(4,           1               ,'Bruwer',                  105);
insert into metadata values(5,           4               ,'AB1234567/4',             105);
insert into metadata values(6,           6               ,'Annuity Tax Directive',   105);
insert into metadata values(7,           3               ,'50594644',                105);
insert into metadata values(8,           2               ,'ABC',                     105);
insert into metadata values(9,           5               ,'Jami',                    105);
insert into metadata values(10,          1               ,'Leigh',                   105);
insert into metadata values(11,          4               ,'9401213056080',           105);
insert into metadata values(12,          6               ,'Redemption',              105);
insert into metadata values(13,          3               ,'50550026',                106);
insert into metadata values(14,          2               ,'ABC',                     106);
insert into metadata values(15,          5               ,'John',                    106);
insert into metadata values(16,          1               ,'Smith',                   106);
insert into metadata values(17,          4               ,'6812305023089',           106);
insert into metadata values(18,          6               ,'Tax Free Transfer Out',   106);
18 rows affected

create table MetadataKey(IdMetadataKey int,   Description varchar(50));
insert into MetadataKey values(1,               'LAST_NAME');
insert into MetadataKey values(2,               'COMPANY');
insert into MetadataKey values(3,               'INVESTOR_ID');
insert into MetadataKey values(4,               'IDENTIFICATION_NUMBER');
insert into MetadataKey values(5,               'FIRST_NAME');
insert into MetadataKey values(6,               'WORK_DESCRIPTION');
6 rows affected

select max(Investor_Id)Investor_Id, max(First_Name)First_Name,  max(Last_Name)Last_Name,   max(IDENTIFICATION_NUMBER),    max(Work_Description)Work_Description,       max(Company)Company, max(IdBatch)IdBatch
from
(
  select md.*, mdk.Description,row_number()over(partition by idbatch,md.idmetadatakey order by idmetadata)rnk from metadata md inner join metadatakey mdk on md.idMetadataKey=mdk.idMetadataKey
) As SourceTable
Pivot
(
max(value) for Description in (Investor_Id, First_Name,  Last_Name,   IDENTIFICATION_NUMBER,    Work_Description,       Company)
) as P
Group by IdBatch,rnk
order by idbatch,investor_id


Investor_Id
First_Name
Last_Name
(No column name)
Work_Description
Company
IdBatch


50550000
Buks
Bruwer
AB1234567/4
Annuity Tax Directive
ABC
105

50594644
Jami
Leigh
9401213056080
Redemption
ABC
105

50550026
John
Smith
6812305023089
Tax Free Transfer Out
ABC
106


Warning: Null value is eliminated by an aggregate or other SET operation.
Investor_Id	First_Name	Last_Name	(No column name)	Work_Description	Company	IdBatch
50550000	Buks	Bruwer	AB1234567/4	Annuity Tax Directive	ABC	105
50594644	Jami	Leigh	9401213056080	Redemption	ABC	105
50550026	John	Smith	6812305023089	Tax Free Transfer Out	ABC	106