Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table parameter_test
 (
  site_id       VARCHAR(30),
  parameter     VARCHAR(30),
  value         NUMBER,
  method        VARCHAR(30)
);
insert into parameter_test ( site_id, parameter, value, method )
SELECT 'site1', 'ANC', 10.1,'Gran 1952' FROM DUAL UNION ALL
SELECT 'site2', 'ANC', 21.8, 'Gran 1952' FROM DUAL UNION ALL
SELECT 'site1', 'NO3', 2.1, 'EPA 1983' FROM DUAL UNION ALL
SELECT 'site2', 'NO3', 7.2, 'EPA 1983' FROM DUAL;
4 rows affected

CREATE TABLE parameter_pivot_test AS
SELECT site_id,
       anc_value AS anc,
       no3_value AS noc,
       anc_method,
       no3_method
FROM   parameter_test
PIVOT (
  SUM( value ) AS value,
  MAX( method ) AS method
  FOR parameter IN (
   'ANC' AS ANC,
   'NO3' AS NO3
  )
);
2 rows affected

SELECT * FROM parameter_pivot_test;


SITE_ID
ANC
NOC
ANC_METHOD
NO3_METHOD


site2
21.8
7.2
Gran 1952
EPA 1983

site1
10.1
2.1
Gran 1952
EPA 1983