UNPIVOT 을 이용하시면 됩니다.

아래 예제 참고 하세요.

with number_table as (
    select 876 HIT_EP, 5 HIT_NUMBER1, 16 HIT_NUMBER2, 21 HIT_NUMBER3, 26 HIT_NUMBER4, 34HIT_NUMBER5, 42 HIT_NUMBER6, 24 HIT_BOUNS FROM DUAL
    UNION ALL
    select 875, 19         ,  22        ,    30     ,      34     ,      39    ,      44   ,    36 FROM DUAL
    UNION ALL
    select 874, 1           ,  15        ,    19     ,      23     ,      28    ,      42   ,    32 FROM DUAL
    UNION ALL
    select 873, 3           ,  5         ,    12     ,      13     ,      33    ,      39   ,    38 FROM DUAL
    UNION ALL
    select 872, 2           ,  4         ,    30     ,      32     ,      33    ,      43   ,    29 FROM DUAL
)
SELECT HIT_NUMBER, COUNT(*) CNT
FROM (
    SELECT *
    FROM number_table
    UNPIVOT ( HIT_NUMBER FOR COL_NM IN (HIT_NUMBER1, HIT_NUMBER2, HIT_NUMBER3, HIT_NUMBER4, HIT_NUMBER5, HIT_NUMBER6, HIT_BOUNS) )
) GROUP BY HIT_NUMBER
ORDER BY 1
; 

박인호 님이 주신 쿼리는 잘 보았습니다.

주신 쿼리에서 row_number를 이용해서 rk를 만들어서

등수 체크하고 상위 6개만 뽑을려고 하니까

where rk가 안되는데 아래 처럼 사용하는것이 아닐까요?

SELECT HIT_NUMBER, COUNT(*) CNT, ROW_NUMBER() OVER (ORDER BY count(*) desc) as rk
FROM (
    SELECT *
    FROM (select * from number_table
    where rownum <=10)
    UNPIVOT ( HIT_NUMBER FOR COL_NM IN (HIT_NUMBER1, HIT_NUMBER2, HIT_NUMBER3, HIT_NUMBER4, HIT_NUMBER5, HIT_NUMBER6) )
   
)
where rk <= 3
GROUP BY HIT_NUMBER
ORDER BY cnt desc;

select *
from (
	SELECT HIT_NUMBER, COUNT(*) CNT
	 FROM (
	     SELECT *
	     FROM (select * from number_table 
	     where rownum <=10)
	     UNPIVOT ( HIT_NUMBER FOR COL_NM IN (HIT_NUMBER1, HIT_NUMBER2, HIT_NUMBER3, HIT_NUMBER4, HIT_NUMBER5, HIT_NUMBER6) )
	     
	 ) 
	 GROUP BY HIT_NUMBER
	 ORDER BY cnt desc
 )
 where rownum <= 6
 ;