SELECT CONCAT(  CASE WHEN A = 'YES' THEN 'A' END,
                ',' + CASE WHEN B = 'YES' THEN 'B' END,
                ',' + CASE WHEN C = 'YES' THEN 'C' END,
                ',' + CASE WHEN D = 'YES' THEN 'D' END,
                ',' + CASE WHEN E = 'YES' THEN 'E' END) NEW
FROM dbo.YourTable
;