WITH A3 ("설문조사번호", "질문번호", SANS) AS (
    SELECT 50,1,'1'       FROM DUAL UNION ALL
    SELECT 50,2,'3'       FROM DUAL UNION ALL
    SELECT 50,3,'1|3'     FROM DUAL UNION ALL
    SELECT 50,4,'1|3|5'   FROM DUAL UNION ALL
    SELECT 50,1,'2'       FROM DUAL UNION ALL
    SELECT 50,2,'4'       FROM DUAL UNION ALL
    SELECT 50,3,'1|2|3'   FROM DUAL UNION ALL
    SELECT 50,4,'1|2|3|4' FROM DUAL UNION ALL
    SELECT 50,1,'999'     FROM DUAL UNION ALL
    SELECT 50,2,'5'       FROM DUAL UNION ALL
    SELECT 50,3,'1|2'     FROM DUAL UNION ALL
    SELECT 50,4,'3|5|999' FROM DUAL
)
SELECT * FROM (
    SELECT "설문조사번호", "질문번호"
        , SUM(REGEXP_COUNT(SANS, 1))  AS CNT1
        , SUM(REGEXP_COUNT(SANS, 2))  AS CNT2
        , SUM(REGEXP_COUNT(SANS, 3) ) AS CNT3
        , SUM(REGEXP_COUNT(SANS, 4) ) AS CNT4
        , SUM(REGEXP_COUNT(SANS, 5) ) AS CNT5
        , SUM(REGEXP_COUNT(SANS, 999))AS CNT999
    FROM A3
    GROUP BY "설문조사번호", "질문번호"
) 
UNPIVOT ( "답변수" FOR "질문답" IN ( CNT1 AS '1' ,CNT2 AS '2',CNT3 AS '3',CNT4 AS '4',CNT5 AS '5',CNT999 AS '기타(999)' ) )
WHERE NOT ( 질문답 = '기타(999)' AND 답변수 = 0 )
ORDER BY "설문조사번호", "질문번호", "질문답"

WITH a1 AS
(
SELECT 50 survey_no, 1 question_no, 'Y' etc FROM dual
UNION ALL SELECT 50, 2, 'N' FROM dual
UNION ALL SELECT 50, 3, 'N' FROM dual
UNION ALL SELECT 50, 4, 'Y' FROM dual
)
, a2 AS
(
SELECT 50 survey_no, 1 question_no, 1 survey_questions FROM dual
UNION ALL SELECT 50, 1, 2 FROM dual
UNION ALL SELECT 50, 1, 3 FROM dual
UNION ALL SELECT 50, 1, 4 FROM dual
UNION ALL SELECT 50, 1, 5 FROM dual
UNION ALL SELECT 50, 2, 1 FROM dual
UNION ALL SELECT 50, 2, 2 FROM dual
UNION ALL SELECT 50, 2, 3 FROM dual
UNION ALL SELECT 50, 2, 4 FROM dual
UNION ALL SELECT 50, 2, 5 FROM dual
UNION ALL SELECT 50, 3, 1 FROM dual
UNION ALL SELECT 50, 3, 2 FROM dual
UNION ALL SELECT 50, 3, 3 FROM dual
UNION ALL SELECT 50, 3, 4 FROM dual
UNION ALL SELECT 50, 3, 5 FROM dual
UNION ALL SELECT 50, 4, 1 FROM dual
UNION ALL SELECT 50, 4, 2 FROM dual
UNION ALL SELECT 50, 4, 3 FROM dual
UNION ALL SELECT 50, 4, 4 FROM dual
UNION ALL SELECT 50, 4, 5 FROM dual
)
, a3 AS
(
SELECT 50 survey_no, 1 question_no, '1' survey_answer, '홍길동' answer FROM dual
UNION ALL SELECT 50, 2, '3'      , '홍길동' FROM dual
UNION ALL SELECT 50, 3, '1|3'    , '홍길동' FROM dual
UNION ALL SELECT 50, 4, '1|3|5'  , '홍길동' FROM dual
UNION ALL SELECT 50, 1, '2'      , '둘리'   FROM dual
UNION ALL SELECT 50, 2, '4'      , '둘리'   FROM dual
UNION ALL SELECT 50, 3, '1|2|3'  , '둘리'   FROM dual
UNION ALL SELECT 50, 4, '1|2|3|4', '둘리'   FROM dual
UNION ALL SELECT 50, 1, '999'    , '또치'   FROM dual
UNION ALL SELECT 50, 2, '5'      , '또치'   FROM dual
UNION ALL SELECT 50, 3, '1|2'    , '또치'   FROM dual
UNION ALL SELECT 50, 4, '3|5|999', '또치'   FROM dual
)
SELECT a0.survey_no, a0.question_no, a0.survey_questions
     , COUNT(a3.survey_answer) survey_answer_cnt  -- 답변 카운트
  FROM (-- a2 의 항목에 a1 이 기타인 경우 999 추가
        SELECT survey_no, question_no, survey_questions
          FROM a2
         UNION ALL
        SELECT survey_no, question_no, 999 survey_questions
          FROM a1
         WHERE etc = 'Y'
        ) a0
  LEFT OUTER JOIN a3  -- a3 와 아우터 조인
    ON a0.survey_no = a3.survey_no
   AND a0.question_no = a3.question_no
   AND INSTR('|'||a3.survey_answer||'|', '|'||a0.survey_questions||'|') > 0 -- 문항 번호 있는지?
 WHERE a0.survey_no = 50  -- 설문번호 조건
 GROUP BY a0.survey_no, a0.question_no, a0.survey_questions  -- 그룹핑
 ORDER BY a0.survey_no, a0.question_no, a0.survey_questions  -- 정렬
;