CASE문과 substr만 쓰시면 될 것 같습니다.


WITH TEMP AS (
    SELECT 'DOC1' KEYCOL, 'YXXXXXX' TARVAL FROM DUAL UNION ALL
    SELECT 'DOC2' KEYCOL, 'XYXXXXX' TARVAL FROM DUAL UNION ALL
    SELECT 'DOC3' KEYCOL, 'XYXXXXY' TARVAL FROM DUAL
)

SELECT DECODE(GROUPING(RNUM),1, '합계', max(keycol)) AS keycol
        ,DECODE(GROUPING(RNUM),1, sum(첫째), max(첫째)) AS 첫째
        ,DECODE(GROUPING(RNUM),1, sum(둘째), max(둘째)) AS 둘째
        ,DECODE(GROUPING(RNUM),1, sum(셋째), max(셋째)) AS 셋째
        ,DECODE(GROUPING(RNUM),1, sum(넷째), max(넷째)) AS 넷째
        ,DECODE(GROUPING(RNUM),1, sum(다섯째), max(다섯째)) AS 다섯째
        ,DECODE(GROUPING(RNUM),1, sum(여섯째), max(여섯째)) AS 여섯째
        ,DECODE(GROUPING(RNUM),1, sum(일곱째), max(일곱째)) AS 일곱째
FROM(        
    SELECT keycol
        ,CASE WHEN substr(TARVAL,1,1) = 'Y' THEN 1 ELSE 0 END AS "첫째"
        ,CASE WHEN substr(TARVAL,2,1) = 'Y' THEN 1 ELSE 0 END AS "둘째"
        ,CASE WHEN substr(TARVAL,3,1) = 'Y' THEN 1 ELSE 0 END AS "셋째"
        ,CASE WHEN substr(TARVAL,4,1) = 'Y' THEN 1 ELSE 0 END AS "넷째"
        ,CASE WHEN substr(TARVAL,5,1) = 'Y' THEN 1 ELSE 0 END AS "다섯째"
        ,CASE WHEN substr(TARVAL,6,1) = 'Y' THEN 1 ELSE 0 END AS "여섯째"
        ,CASE WHEN substr(TARVAL,7,1) = 'Y' THEN 1 ELSE 0 END AS "일곱째"
        ,ROWNUM AS RNUM
    FROM TEMP 
) T
GROUP BY ROLLUP(RNUM)    

SELECT keycol
     , COUNT(DECODE(SUBSTR(tarval, 1, 1), 'Y', 1)) cnt1
     , COUNT(DECODE(SUBSTR(tarval, 2, 1), 'Y', 1)) cnt2
     , COUNT(DECODE(SUBSTR(tarval, 3, 1), 'Y', 1)) cnt3
     , COUNT(DECODE(SUBSTR(tarval, 4, 1), 'Y', 1)) cnt4
     , COUNT(DECODE(SUBSTR(tarval, 5, 1), 'Y', 1)) cnt5
     , COUNT(DECODE(SUBSTR(tarval, 6, 1), 'Y', 1)) cnt6
     , COUNT(DECODE(SUBSTR(tarval, 7, 1), 'Y', 1)) cnt7
  FROM temp
 GROUP BY ROLLUP(keycol)
;