How do I assign methods to a specific parameter using PIVOT in SQL?

We’re working on producing our data as a relational database/set and as a row-column dataset (so that the users of the data can use which format suits them best). Here’s a simplified table of our data:

drop table parameter_test;
create table parameter_test
 (
  site_id       VARCHAR(30),
  parameter     VARCHAR(30),
  value         NUMBER,
  method        VARCHAR(30)
);

insert into parameter_test values ('site1', 'ANC', 10.1,'Gran 1952');
insert into parameter_test values('site2', 'ANC', 21.8, 'Gran 1952');
insert into parameter_test values('site1', 'NO3', 2.1, 'EPA 1983');
insert into parameter_test values('site2', 'NO3', 7.2, 'EPA 1983');

I know how to pivot for the values, but this doesn’t put the method into a specific parameter_column (ANC_method or NO3 method):

drop table parameter_pivot_test;
create table parameter_pivot_test
AS
(select * from (select site_id, parameter_number, parameter
   from parameter_test) 
   pivot (sum(parameter_number) for parameter in (
'ANC'   ANC,
'NO3'   NO3
)))
;

But I want to get the following table:

drop table parameter_pivot;
create table parameter_pivot
 (
  site_id       VARCHAR(30),
  ANC           NUMBER,
  NO3           NUMBER,
  ANC_method    VARCHAR(30),
  NO3_method    VARCHAR(30)
);

insert into parameter_pivot values ('site1', 10.1, 2.1, 'Gran 1952', 'EPA 1983');
insert into parameter_pivot values ('site2', 21.8, 7.2, 'Gran 1952', 'EPA 1983');

How do I get the specific methods to attach to the specific parameter as a new column? I feel like it might be some sort of group by statement, but not sure where to start.

Thanks!

Answer

I would just use conditional aggregation:

select
    site_id,
    max(case when parameter = 'ANC' then value end) anc,
    max(case when parameter = 'NO3' then value end) no3,
    max(case when parameter = 'ANC' then method end) anc_method,
    max(case when parameter = 'NO3' then method end) no3_method
from parameter_pivot
group by side_id

This pivots the data over a fixed list of columns. If you want a dynamic pivot, then you need dynamic SQL, which is a different beast.

Advertisement

Answer