Why is my unpivot query on Athena not working? I just followed the samples online but i always get `-CodePudding

I have a table that looks like

date	oil	gas	petroleum
2022-01	100	90	80
2022-02	90	90	90

and using this query:

select * from source.table UNPIVOT INCLUDE NULLS
(value for type in ("oil", "gas", "petroleum"))

I want it to look like

date	type	value
2022-01	oil	100
2022-01	gas	90
2022-01	petroleum	80
2022-02	oil	90
2022-02	gas	90
2022-02	petroleum	90

but instead I'm getting this error

line 1:52: mismatched input 'INCLUDE'. Expecting: '(', ',', 'CROSS', 'EXCEPT', 'FULL', 'GROUP', 'HAVING', 'INNER', 'INTERSECT', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'OFFSET', 'ORDER', 'RIGHT', 'TABLESAMPLE', 'UNION', 'WHERE', <EOF>

CodePudding user response：

There is no UNPIVOT function in Presto/Trino on which AWS Athena is based. You need to unpivot "manually", for example using arrays and unnest:

-- sample data
with dataset(date, oil, gas, petroleum) as(
    values ('2022-01', 100, 90, 80),
        ('2022-02', 90, 90, 90)
)

-- query
select date, type, value
from dataset,
    unnest(array['oil', 'gas', 'petroleum'], array[oil, gas, petroleum]) as t(type, value);

Output:

date	type	value
2022-01	oil	100
2022-01	gas	90
2022-01	petroleum	80
2022-02	oil	90
2022-02	gas	90
2022-02	petroleum	90