1  with manufacturer
  2  as (select id mid, param_val manf from tb_test_01 where param_nm = 'MANUFACTURER'),
  3      COUNTRY
  4  AS (SELECT id cid, PARAM_VAL CNTY FROM tb_test_01 WHERE PARAM_NM = 'COUNTRY')
  5* select max(mid), m.manf, c.cnty from manufacturer m, country c where mid = cid group by m.manf, c.cnty
SQL> /

  MAX(MID) MANF                     CNTY
---------- ------------------------ ------------------------
        70 HONDA                    UK
        10 NISSAN                   USA
        30 NISSAN                   UK
        80 HONDA                    USA

WITH 
manufacturer AS ( SELECT ID mid, param_val manf
                  FROM   tb_test_01
                  WHERE  param_nm = 'MANUFACTURER'
                  AND    param_val = 'HONDA'
                ),
country AS ( SELECT ID cid, param_val cnty 
             FROM   tb_test_01 
             WHERE  param_nm = 'COUNTRY'
             AND    param_val = 'USA'
           )
SELECT MAX(mid), m.manf, c.cnty
FROM   manufacturer m, country c
WHERE  mid = cid
GROUP BY m.manf, c.cnty;