비번을 까먹은 관계로... 여기다가 수정된 내용을 올립니다.

WITH nemo AS
(
SELECT '○' X1,'○' X2,'○' X3,' ' X4,' ' X5,' ' X6,' ' X7,'○' X8,'○' X9,' ' X10 FROM DUAL UNION ALL
SELECT '○' X1,'○' X2,' ' X3,' ' X4,' ' X5,' ' X6,' ' X7,' ' X8,' ' X9,' ' X10 FROM DUAL UNION ALL
SELECT ' ' X1,'○' X2,' ' X3,' ' X4,' ' X5,'○' X6,' ' X7,' ' X8,' ' X9,' ' X10 FROM DUAL UNION ALL
SELECT ' ' X1,' ' X2,' ' X3,' ' X4,' ' X5,' ' X6,' ' X7,' ' X8,' ' X9,' ' X10 FROM DUAL UNION ALL
SELECT ' ' X1,' ' X2,' ' X3,' ' X4,'○' X5,'○' X6,' ' X7,' ' X8,' ' X9,' ' X10 FROM DUAL UNION ALL
SELECT '○' X1,' ' X2,' ' X3,' ' X4,' ' X5,' ' X6,' ' X7,' ' X8,' ' X9,' ' X10 FROM DUAL UNION ALL
SELECT ' ' X1,'○' X2,' ' X3,'○' X4,'○' X5,'○' X6,'○' X7,'○' X8,'○' X9,' ' X10 FROM DUAL UNION ALL
SELECT ' ' X1,' ' X2,' ' X3,' ' X4,'○' X5,' ' X6,' ' X7,'○' X8,' ' X9,' ' X10 FROM DUAL UNION ALL
SELECT '○' X1,' ' X2,'○' X3,' ' X4,' ' X5,' ' X6,' ' X7,' ' X8,' ' X9,'○' X10 FROM DUAL UNION ALL
SELECT ' ' X1,' ' X2,' ' X3,'○' X4,' ' X5,'○' X6,' ' X7,' ' X8,' ' X9,' ' X10 FROM DUAL
)
select max(x1) x1,
max(x2) x2,
max(x3) x3,
max(x4) x4,
max(x5) x5,
max(x6) x6,
max(x7) x7,
max(x8) x8,
max(x9) x9,
max(x10) x10,
max(x) x
from (
select a.rn,
b.no,
a.x1,a.x2,a.x3,a.x4,a.x5,a.x6,a.x7,a.x8,a.x9,a.x10,
wm_concat(length(regexp_substr(x,'[^ ]+',1,b.no))) over (partition by a.rn order by b.no) x
from (
select rownum rn,
x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,x,
nvl(regexp_count(x,' '),0) + 1 cnt
from (select x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,
trim(regexp_replace(x1||x2||x3||x4||x5||x6||x7||x8||x9||x10,'( ){2,}',' ')) x from nemo)
) a inner join (select level no from dual connect by level <= 10) b
on a.cnt >= b.no
)
group by rn
union all
select wm_concat(length(regexp_substr(x1,'[^,]+',1,level))) x1,
wm_concat(length(regexp_substr(x2,'[^,]+',1,level))) x2,
wm_concat(length(regexp_substr(x3,'[^,]+',1,level))) x3,
wm_concat(length(regexp_substr(x4,'[^,]+',1,level))) x4,
wm_concat(length(regexp_substr(x5,'[^,]+',1,level))) x5,
wm_concat(length(regexp_substr(x6,'[^,]+',1,level))) x6,
wm_concat(length(regexp_substr(x7,'[^,]+',1,level))) x7,
wm_concat(length(regexp_substr(x8,'[^,]+',1,level))) x8,
wm_concat(length(regexp_substr(x9,'[^,]+',1,level))) x9,
wm_concat(length(regexp_substr(x10,'[^,]+',1,level))) x10,
' ' x
from (
select
regexp_replace(replace(wm_concat(x1),',',''),'( ){1,}',',') x1,
regexp_replace(replace(wm_concat(x2),',',''),'( ){1,}',',') x2,
regexp_replace(replace(wm_concat(x3),',',''),'( ){1,}',',') x3,
regexp_replace(replace(wm_concat(x4),',',''),'( ){1,}',',') x4,
regexp_replace(replace(wm_concat(x5),',',''),'( ){1,}',',') x5,
regexp_replace(replace(wm_concat(x6),',',''),'( ){1,}',',') x6,
regexp_replace(replace(wm_concat(x7),',',''),'( ){1,}',',') x7,
regexp_replace(replace(wm_concat(x8),',',''),'( ){1,}',',') x8,
regexp_replace(replace(wm_concat(x9),',',''),'( ){1,}',',') x9,
regexp_replace(replace(wm_concat(x10),',',''),'( ){1,}',',') x10
from nemo
)
connect by level <= 10