WITH data_t AS
(
SELECT '해병' name, 'H1' hgroup, 1 lgroup, 'Pam' item FROM dual
UNION ALL SELECT '해병', 'H2' ,  2, 'Cm'     FROM dual
UNION ALL SELECT '해병', 'H2' ,  3, 'Das'    FROM dual
UNION ALL SELECT '해병', 'H3' ,  5, 'IPaq'   FROM dual
UNION ALL SELECT '해병', 'H4' ,  6, 'Goal'   FROM dual
UNION ALL SELECT '해병', 'H4' ,  7, 'Leg'    FROM dual
UNION ALL SELECT '해병', 'H6' ,  8, 'Power'  FROM dual
UNION ALL SELECT '해병', 'H6' ,  9, 'Stick'  FROM dual
UNION ALL SELECT '해병', 'H7' , 10, 'Mental' FROM dual
UNION ALL SELECT '해병', 'H10', 11, 'Siru'   FROM dual
UNION ALL SELECT '해병', 'H10', 24, 'Siru'   FROM dual
UNION ALL SELECT '육군', 'H1' ,  1, 'aaaa'   FROM dual
)
SELECT h.name
     , h.hgroup
     , l.lgroup
  FROM (SELECT b.name
             , LISTAGG(NVL(b.hgroup, 'X'||a.lv), ' ') WITHIN GROUP(ORDER BY a.lv) hgroup
          FROM (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10) a
          LEFT OUTER JOIN (SELECT DISTINCT name, hgroup FROM data_t) b
          PARTITION BY (b.name)
            ON 'H'||a.lv = b.hgroup
         GROUP BY b.name
        ) h
     , (SELECT b.name
             , LISTAGG(NVL(b.lgroup||'', 'X'||a.lv), ' ') WITHIN GROUP(ORDER BY a.lv) lgroup
          FROM (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 12) a  -- :p_to
          LEFT OUTER JOIN data_t b
          PARTITION BY (b.name)
            ON a.lv = b.lgroup
         GROUP BY b.name
        ) l
 WHERE h.name = l.name
;