Pivot rows into columns in AWS Athena

Question

I'm trying to pivot some rows into columns

When I tried:

SELECT column1, column2, column3
FROM data
PIVOT
(
  MIN(column3)
  FOR column2 IN ('VALUE1','VALUE2','VALUE3','VALUE4')
)

I get the error:

mismatched input '(' expecting {',', ')'} (service: amazonathena; status code: 400; error code: invalidrequestexception

Can anyone help me in this?

Answer 1

You can use the following method :-

It worked well for me.

SELECT
  uid,
  kv['c1'] AS c1,
  kv['c2'] AS c2,
  kv['c3'] AS c3
FROM (
  SELECT uid, map_agg(key, value) kv
  FROM vtable
  GROUP BY uid
)