일단 저도 초보인점 밝히구요...

오라클을 사용해본적이 없어

MSSQL 기준으로 작성되었습니다..^^;;

최적화된 쿼리도 있겠지만..아래 쿼리가 현재 제 한계네요-_-;;;

참고만 하세요...

---쿼리시작---

;with 회원(name,age,country,탈퇴사유) As
(
      Select '홍길동1',21,'한국','001,002,003,004'     From tdual  Union All
      Select '홍길동2',22,'미국','002'                 From tdual  Union All
      Select '홍길동3',23,'호주','004,005,006,007,008' From tdual
)
,회원2(seq,name,age,country,탈퇴사유o,탈퇴사유l,탈퇴사유) As
(
      Select 1
            ,a.name
            ,a.age
            ,a.country
            ,a.탈퇴사유
            ,SUBSTRING(a.탈퇴사유,(
                                        Case When IsNull(CharIndex(',',a.탈퇴사유),0) = 0 Then 0
                                             Else IsNull(CharIndex(',',a.탈퇴사유),0) +1
                                        End
                                  )
                                 ,
                                  (
                                        Case When IsNull(CharIndex(',',a.탈퇴사유),0) = 0 Then 0
                                             Else LEN(a.탈퇴사유)
                                        End
                                  )
                     )
            ,SUBSTRING(a.탈퇴사유,1,(
                                        Case When IsNull(CharIndex(',',a.탈퇴사유),0) = 0 Then Len(a.탈퇴사유)
                                             Else IsNull(CharIndex(',',a.탈퇴사유),0) -1
                                        End
                                    )
                      )
        From 회원 a

   Union All

      Select a.seq + 1
            ,a.name
            ,a.age
            ,a.country
            ,a.탈퇴사유o
            ,SUBSTRING(a.탈퇴사유l,(
                                        Case When IsNull(CharIndex(',',a.탈퇴사유l),0) = 0 Then 0
                                             Else IsNull(CharIndex(',',a.탈퇴사유l),0) +1
                                        End
                                  )
                                 ,
                                  (
                                        Case When IsNull(CharIndex(',',a.탈퇴사유l),0) = 0 Then 0
                                             Else LEN(a.탈퇴사유l)
                                        End
                                  )
                     )
            ,SUBSTRING(a.탈퇴사유l,1,(
                                        Case When IsNull(CharIndex(',',a.탈퇴사유l),0) = 0 Then Len(a.탈퇴사유l)
                                             Else IsNull(CharIndex(',',a.탈퇴사유l),0) -1
                                        End
                                    )
                      )
        From 회원2 a
       Where IsNull(a.탈퇴사유l,'') <> ''
)       
,공통테이블(SCODE,탈퇴사유) As
(
      Select '001','그냥'      From tdual Union All
      Select '002','모름1'     From tdual Union All
      Select '003','모름2'     From tdual Union All
      Select '004','모름3'     From tdual Union All
      Select '005','모름4'     From tdual Union All
      Select '006','모름5'     From tdual Union All
      Select '007','모름6'     From tdual Union All
      Select '008','계속모름7' From tdual
)
,회원3(seq,name,age,country,SCODE,탈퇴사유) As
(
      Select 1
            ,a.name
            ,a.age
            ,a.country
            ,Convert(varchar(max),a.탈퇴사유)
            ,Convert(varchar(max),b.탈퇴사유)
        From 회원2 a Inner Join 공통테이블 b On a.탈퇴사유 = b.SCODE
       Where a.seq = 1

   Union All      
      
      Select a.seq
            ,a.name
            ,a.age
            ,a.country
            ,Convert(varchar(max),c.SCODE + ',' + a.탈퇴사유 )
            ,Convert(varchar(max),c.탈퇴사유 + ',' + b.탈퇴사유  )
        From 회원2 a Inner Join 공통테이블 b On a.탈퇴사유 = b.SCODE
                     Inner Join 회원3 c On a.seq = c.seq + 1
                                       And a.name = c.name
                                       And a.age = c.age
                                       And a.country = c.country
)      

    Select a.name
          ,a.age
          ,a.country
          ,a.SCODE As SCODE
          ,MAX(a.탈퇴사유) As 탈퇴사유
      From 회원3 a
     Where a.seq = (
                      Select
                       Top 1 x.seq
                        From 회원3 x
                       Where a.age = x.age
                         And a.country = x.country
                         And a.name = x.name
                    Order By x.seq Desc
                   )
  Group By a.name
          ,a.age
          ,a.country
          ,a.SCODE
         
---쿼리끝---

WITH code_t AS
(
SELECT '001' scode, '그냥' sname FROM dual
UNION ALL SELECT '002', '모름1' FROM dual
UNION ALL SELECT '003', '모름2' FROM dual
UNION ALL SELECT '004', '모름3' FROM dual
UNION ALL SELECT '005', '모름4' FROM dual
UNION ALL SELECT '006', '모름5' FROM dual
UNION ALL SELECT '007', '모름6' FROM dual
UNION ALL SELECT '008', '모름7' FROM dual
)
, data_t AS
(
SELECT '홍길동2' name, 22 age, '미국' contry, '002' scode FROM dual
UNION ALL SELECT '홍길동1', 21, '한국', '001,002,003,004' FROM dual
UNION ALL SELECT '홍길동3', 23, '호주', '004,005,006,007,008' FROM dual
)
SELECT d.name, d.age, d.contry
     , LISTAGG(c.sname, ',')
       WITHIN GROUP(ORDER BY INSTR(d.scode, c.scode)) sname
  FROM data_t d
     , code_t c
 WHERE INSTR(d.scode, c.scode) > 0
 GROUP BY d.name, d.age, d.contry
 ORDER BY d.name, d.age, d.contry
;