WITH a AS
(
SELECT LEVEL nm
FROM dual
CONNECT BY LEVEL <= 10
)
, b AS
(
SELECT '가' com, 1 h1, 4 h2, 5 h3, 6 h4 FROM dual
UNION ALL SELECT '나', 3, 1, 1, 2 FROM dual
UNION ALL SELECT '다', 2, null, null, 2 FROM dual
UNION ALL SELECT '라', null, 2, null, null FROM dual
UNION ALL SELECT '마', 5, null, 2, null FROM dual
UNION ALL SELECT '바', null, 1, null, 2 FROM dual
UNION ALL SELECT '사', null, null, 3, null FROM dual
UNION ALL SELECT '아', null, 4, null, null FROM dual
UNION ALL SELECT '자', 5, null, 1, null FROM dual
UNION ALL SELECT '차', null, 6, null, 7 FROM dual
)
SELECT a.nm
, COUNT(b.com) cnt
FROM a
LEFT OUTER JOIN b
ON a.nm IN (b.h1, h2, h3, h4)
GROUP BY a.nm
ORDER BY a.nm
;

마농님 감사합니다...이렇게 하는거군요...좋은거 배워 갑니다 ^^

WITH TB_A AS (
SELECT 1 NAME FROM DUAL
UNION ALL SELECT 2 NAME FROM DUAL
UNION ALL SELECT 2 NAME FROM DUAL
UNION ALL SELECT 3 NAME FROM DUAL
UNION ALL SELECT 4 NAME FROM DUAL
UNION ALL SELECT 5 NAME FROM DUAL
UNION ALL SELECT 6 NAME FROM DUAL
UNION ALL SELECT 7 NAME FROM DUAL
UNION ALL SELECT 8 NAME FROM DUAL
UNION ALL SELECT 9 NAME FROM DUAL
UNION ALL SELECT 10 NAME FROM DUAL
), TB_B AS(
SELECT '가' COM, 1 NAME1, 4 NAME2, 5 NAME3, 6 NAME4 FROM DUAL
UNION ALL SELECT '나' COM, 3 , 1 , 1 , 2 FROM DUAL
UNION ALL SELECT '다' COM, 2 , NULL , NULL , 2 FROM DUAL
UNION ALL SELECT '라' COM, NULL , 2 , NULL , NULL FROM DUAL
UNION ALL SELECT '마' COM, 5 , NULL , 2 , NULL FROM DUAL
UNION ALL SELECT '바' COM, NULL , 1 , NULL , 2 FROM DUAL
UNION ALL SELECT '사' COM, NULL , NULL , 3 , NULL FROM DUAL
UNION ALL SELECT '아' COM, NULL , 4 , NULL , NULL FROM DUAL
UNION ALL SELECT '자' COM, 5 , NULL , 1 , NULL FROM DUAL
UNION ALL SELECT '차' COM, NULL , 6 , NULL , 7 FROM DUAL
)
SELECT LV, NVL( COM, 0 ) AS COM
FROM (
SELECT /*+ LEADING( A ) FULL( A ) USE_NL( A B ) PUSH_PRED( B ) */ B.NAME, COUNT( DISTINCT B.COM ) COM
FROM TB_A A
, (SELECT NAME1 NAME, COM FROM TB_B
UNION ALL
SELECT NAME2, COM FROM TB_B
UNION ALL
SELECT NAME3, COM FROM TB_B
UNION ALL
SELECT NAME4, COM FROM TB_B
) B
WHERE A.NAME = B.NAME
GROUP BY B.NAME
) A
, (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10 ) B
WHERE A.NAME(+) = B.LV
ORDER BY LV

WITH TB_A AS (
SELECT 1 NAME FROM DUAL
UNION ALL SELECT 2 NAME FROM DUAL
UNION ALL SELECT 2 NAME FROM DUAL
UNION ALL SELECT 3 NAME FROM DUAL
UNION ALL SELECT 4 NAME FROM DUAL
UNION ALL SELECT 5 NAME FROM DUAL
UNION ALL SELECT 6 NAME FROM DUAL
UNION ALL SELECT 7 NAME FROM DUAL
UNION ALL SELECT 8 NAME FROM DUAL
UNION ALL SELECT 9 NAME FROM DUAL
UNION ALL SELECT 10 NAME FROM DUAL
), TB_B AS(
SELECT '가' COM, 1 NAME1, 4 NAME2, 5 NAME3, 6 NAME4 FROM DUAL
UNION ALL SELECT '나' COM, 3 , 1 , 1 , 2 FROM DUAL
UNION ALL SELECT '다' COM, 2 , NULL , NULL , 2 FROM DUAL
UNION ALL SELECT '라' COM, NULL , 2 , NULL , NULL FROM DUAL
UNION ALL SELECT '마' COM, 5 , NULL , 2 , NULL FROM DUAL
UNION ALL SELECT '바' COM, NULL , 1 , NULL , 2 FROM DUAL
UNION ALL SELECT '사' COM, NULL , NULL , 3 , NULL FROM DUAL
UNION ALL SELECT '아' COM, NULL , 4 , NULL , NULL FROM DUAL
UNION ALL SELECT '자' COM, 5 , NULL , 1 , NULL FROM DUAL
UNION ALL SELECT '차' COM, NULL , 6 , NULL , 7 FROM DUAL
)
SELECT /*+ LEADING( A ) FULL( A ) USE_NL( A B ) PUSH_PRED( B ) */ A.NAME, COUNT( DISTINCT B.COM ) COM
FROM TB_A A
, (SELECT NAME1 NAME, COM FROM TB_B
UNION ALL
SELECT NAME2, COM FROM TB_B
UNION ALL
SELECT NAME3, COM FROM TB_B
UNION ALL
SELECT NAME4, COM FROM TB_B
) B
WHERE A.NAME = B.NAME(+)
GROUP BY A.NAME