demo, Sql Server - rextester

Language: Layout:

create table t (MoveType nvarchar(100) , IsHazardous int, IsReefer   int,  IsOOG int);
insert into t values
 ('DSCR',0,0,0)
,('LOAD',0,0,0)
,('SHFT',0,0,0);

/*
;with t as 
(
    SELECT   MoveType,
         SUM(IsHazardous) AS IsHazardous,
         SUM(IsReefer) AS IsReefer,
         SUM (IsOOG) AS IsOOG
FROM     (SELECT qce.MoveType,
                 (CASE WHEN qce.IsHazardous='YES' THEN 1 ELSE 0 END) AS IsHazardous,
                 (CASE WHEN qce.IsReefer='YES' THEN 1 ELSE 0 END) AS IsReefer,
                 (CASE WHEN qce.IsOOG='YES' THEN 1 ELSE 0 END) AS IsOOG
          FROM   qbtCallEquipmentExecution AS qcee
            INNER JOIN qbtCallEquipment qce ON
                 qce.ID=qcee.CallEquipmentID
            AND  qce.IsActive=1
          WHERE  qce.CallID=1169
          AND qcee.IsActive=1) AS A
GROUP BY MoveType
)
*/
select v as MoveType,DSCR,LOAD,SHFT 
from 
    (
        select * from t

) s
unpivot
    (
        data for v in (IsHazardous,IsReefer, IsOOG)
    )u
pivot 
    (
        sum(data) for MoveType in (DSCR,LOAD,SHFT)
    )p

View schema
Execution time: 0,03 sec, rows selected: 3, rows affected: 3, absolute service time: 0,2 sec, absolute service time: 0,22 sec