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, '매우좋음' survey_name 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, a0.survey_name
     , COUNT(a3.survey_answer) survey_answer_cnt  -- 답변 카운트
  FROM (-- a2 의 항목에 a1 이 기타인 경우 999 추가
        SELECT survey_no, question_no, survey_questions, survey_name
          FROM a2
         UNION ALL
        SELECT survey_no, question_no, 999 survey_questions, '기타' survey_name
          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   -- 설문번호 조건
-- AND a0.question_no = 2  -- 질문번호 조건
 GROUP BY a0.survey_no, a0.question_no, a0.survey_questions, a0.survey_name  -- 그룹핑
 ORDER BY a0.survey_no, a0.question_no, a0.survey_questions                  -- 정렬
;