clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591633 fiddles created (45702 in the last week).

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
 hidden batch(es)


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
 hidden batch(es)


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.
 hidden batch(es)