How to display row values as columns?

Question

I have this table:

FIELD_CODE  PY_DATIME   PARAM_NAME  PARAM_VAL


F262/034    29-JAN-2021 IRRIGATION_AMOUNT   0
F262/034    29-JAN-2021 MAX_TS_20   250
F262/034    29-JAN-2021 MAX_TS_40   168
F262/034    29-JAN-2021 ET  1.616806
F262/034    30-JAN-2021 MDS 25
F262/034    30-JAN-2021 GROWTH  -8.333333
F262/034    30-JAN-2021 PLANT_STATUS    90
F262/034    30-JAN-2021 IRRIGATION_AMOUNT   0
F262/034    30-JAN-2021 MAX_TS_20   81
F262/034    30-JAN-2021 MAX_TS_40   83
F262/034    30-JAN-2021 ET  1.1099839
F262/034    31-JAN-2021 MDS 56.666668
F262/034    31-JAN-2021 GROWTH  18.333334
F262/034    31-JAN-2021 PLANT_STATUS    70
F262/034    31-JAN-2021 IRRIGATION_AMOUNT   0
F262/034    31-JAN-2021 MAX_TS_20   114
F262/034    31-JAN-2021 MAX_TS_40   95
F262/034    31-JAN-2021 ET  2.2792487

I want to display the data like this:

FIELD_CODE PY_DATIME MDS GROWTH  PLANT_STATUS    IRRIGATION_AMOUNT   MAX_TS_20   MAX_TS_40 ET

I need to take the values from the PARAM_NAME column and make them the columns. And the PARAM_VAL as the Value. But display only once the PY_DATIME and the FIELD_CODE.

the Primay Key of the table is (FIELD_CODE, PY_DATIME, PARAM_NAME)

Thanks.

Answer 1

You can use conditional aggregation for this -- assuming that you know the columns you want in advance:

select field_code, py_datetime,
       max(case when param_name = 'MDS' then param_val end) as mds,
       max(case when param_name = 'GROWTH' then param_val end) as GROWTH,
       max(case when param_name = 'PLANT_STATUS' then param_val end) as PLANT_STATUS,
       max(case when param_name = 'IRRIGATION_AMOUNT' then param_val end) as IRRIGATION_AMOUNT,
       max(case when param_name = 'MAX_TS_20' then param_val end) as MAX_TS_20,
       max(case when param_name = 'MAX_TS_40' then param_val end) as MAX_TS_40,
       max(case when param_name = 'ET' then param_val end) as ET
from t
group by field_code, py_datetime;