SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table FACT_TABLE
(
   INSTRUMENT_ID nvarchar(3),
   VALUE_TYPE_CODE nvarchar(10),
   ORIGINAL_TRANSACTION_VALUE int,
   VALUE_IN_HOME_CURRENCY money,
);

insert into FACT_TABLE (INSTRUMENT_ID, VALUE_TYPE_CODE, ORIGINAL_TRANSACTION_VALUE, VALUE_IN_HOME_CURRENCY) values
('ABC', 'TOT_AMT' , 3, 5.7702),
('ABC', 'AMT_AVAL', 1, 1.9234),
('ABC', 'DRWN_AMT', 2, 3.8468),
('ABC', 'MTD_DRWN', 2, 3.8468);

SELECT
   INSTRUMENT_ID,
   VALUE_TYPE_CODE,
   ORIGINAL_TRANSACTION_VALUE,
   VALUE_IN_HOME_CURRENCY
FROM
   FACT_TABLE;


INSTRUMENT_ID
VALUE_TYPE_CODE
ORIGINAL_TRANSACTION_VALUE
VALUE_IN_HOME_CURRENCY


ABC
TOT_AMT
3
5.7702

ABC
AMT_AVAL
1
1.9234

ABC
DRWN_AMT
2
3.8468

ABC
MTD_DRWN
2
3.8468


-- SOLUTION 1

SELECT piv1.INSTRUMENT_ID,
       max(piv1.TOT_AMT)   as TOT_AMT,
       max(piv1.TOT_AMT1)  as TOT_AMT1,
       max(piv1.AMT_AVAL)  as AMT_AVAL,
       max(piv1.AMT_AVAL1) as AMT_AVAL1,
       max(piv1.DRWN_AMT)  as DRWN_AMT,
       max(piv1.DRWN_AMT1) as DRWN_AMT1,
       max(piv1.MTD_DRWN)  as MTD_DRWN,
       max(piv1.MTD_DRWN1) as MTD_DRWN1
FROM
(
  SELECT INSTRUMENT_ID,
       VALUE_TYPE_CODE,
       VALUE_TYPE_CODE+'1' as VALUE_TYPE_CODE1,
       ORIGINAL_TRANSACTION_VALUE,
       VALUE_IN_HOME_CURRENCY
  FROM FACT_TABLE
)
AS T
-- Pivot on ORIGINAL_TRANSACTION_VALUE
PIVOT (MAX (ORIGINAL_TRANSACTION_VALUE) FOR  VALUE_TYPE_CODE IN ([TOT_AMT], 
                                                                 [AMT_AVAL], 
                                                                 [DRWN_AMT], 
                                                                 [MTD_DRWN])) as piv
-- Pivot on VALUE_IN_HOME_CURRENCY
PIVOT (MAX (VALUE_IN_HOME_CURRENCY) FOR  VALUE_TYPE_CODE1 IN ([TOT_AMT1], 
                                                              [AMT_AVAL1], 
                                                              [DRWN_AMT1], 
                                                              [MTD_DRWN1])) as piv1
group by piv1.INSTRUMENT_ID


INSTRUMENT_ID
TOT_AMT
TOT_AMT1
AMT_AVAL
AMT_AVAL1
DRWN_AMT
DRWN_AMT1
MTD_DRWN
MTD_DRWN1


ABC
3
5.7702
1
1.9234
2
3.8468
2
3.8468


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


-- SOLUTION 2

with cte_piv as
(
  -- Pivot on ORIGINAL_TRANSACTION_VALUE
  select piv.INSTRUMENT_ID,
         piv.TOT_AMT,
         piv.AMT_AVAL,
         piv.DRWN_AMT,
         piv.MTD_DRWN
  from ( SELECT INSTRUMENT_ID, VALUE_TYPE_CODE, ORIGINAL_TRANSACTION_VALUE
         FROM FACT_TABLE ) T
  PIVOT (MAX (ORIGINAL_TRANSACTION_VALUE) FOR  VALUE_TYPE_CODE IN ([TOT_AMT], 
                                                                 [AMT_AVAL], 
                                                                 [DRWN_AMT], 
                                                                 [MTD_DRWN])) as piv
),
cte_piv1 as
(
  -- Pivot on VALUE_IN_HOME_CURRENCY
  select piv1.INSTRUMENT_ID,
         piv1.TOT_AMT,
         piv1.AMT_AVAL,
         piv1.DRWN_AMT,
         piv1.MTD_DRWN
  from ( SELECT INSTRUMENT_ID, VALUE_TYPE_CODE, VALUE_IN_HOME_CURRENCY
         FROM FACT_TABLE ) T
  PIVOT (MAX (VALUE_IN_HOME_CURRENCY) FOR  VALUE_TYPE_CODE IN ([TOT_AMT], 
                                                              [AMT_AVAL], 
                                                              [DRWN_AMT], 
                                                              [MTD_DRWN])) as piv1
)
select p.INSTRUMENT_ID,
       p.TOT_AMT,
       p1.TOT_AMT,
       p.AMT_AVAL,
       p1.AMT_AVAL,
       p.DRWN_AMT,
       p1.DRWN_AMT,
       p.MTD_DRWN,
       p1.MTD_DRWN
from cte_piv p
join cte_piv1 p1 on p1.INSTRUMENT_ID = p.INSTRUMENT_ID


INSTRUMENT_ID
TOT_AMT
TOT_AMT
AMT_AVAL
AMT_AVAL
DRWN_AMT
DRWN_AMT
MTD_DRWN
MTD_DRWN


ABC
3
5.7702
1
1.9234
2
3.8468
2
3.8468
INSTRUMENT_ID	VALUE_TYPE_CODE	ORIGINAL_TRANSACTION_VALUE	VALUE_IN_HOME_CURRENCY
ABC	TOT_AMT	3	5.7702
ABC	AMT_AVAL	1	1.9234
ABC	DRWN_AMT	2	3.8468
ABC	MTD_DRWN	2	3.8468