SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
Msg 102 Level 15 State 1 Line 1
Incorrect syntax near 'SESSION'.

CREATE TABLE Accounts_Download (
    ref           VARCHAR(30),
    subref       VARCHAR(30),
    division  VARCHAR(30),
    profit int
);
INSERT INTO Accounts_Download VALUES('MCA042G21AA','MVCA1','SALES',7938);
INSERT INTO Accounts_Download VALUES('MCA042G21AA','MVCA2','PURCHASING',1984);
2 rows affected

select ref,min(sales)SALES,min(purchasing) PURCHASING,sum(profit) profit from (
SELECT  Ref,
        COALESCE([SALES],'N/A') AS [SALES],
        COALESCE([PURCHASING], 'N/A') AS [PURCHASING],profit

FROM

(SELECT Ref,
        SubRef,
        Division,
        profit
FROM Accounts_Download

WHERE Ref = 'MCA042G21AA'

) AS SourceTable

Pivot (MAX(SubRef)  
For Division in ([SALES],[PURCHASING])) AS CvgPivot) as a
group by ref


ref
SALES
PURCHASING
profit


MCA042G21AA
MVCA1
MVCA2
9922