How to pivot rows into columns in AWS Athena?

Question

I'm new to AWS Athena and trying to pivot some rows into columns, similar to the top answer in this StackOverflow post.

However, 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

Does anyone know how to accomplish what I am trying to achieve in AWS Athena?

Answer 1

Extending @kadrach 's answer. Assuming a table like this

uid | key | value1 | value2
----+-----+--------+--------
 1  |  A  |  10    | 1000
 1  |  B  |  20    | 2000
 2  |  A  |  11    | 1001
 2  |  B  |  21    | 2001

Single column PIVOT works like this

SELECT
  uid,
  kv1['A'] AS A_v1,
  kv1['B'] AS B_v1
FROM (
  SELECT uid, map_agg(key, value1) kv1
  FROM vtable
  GROUP BY uid
) 

Result:

uid | A_v1 | B_v1 
----+------+-------
 1  |  10  |  20   
 2  |  11  |  21  

Multi column PIVOT works like this

SELECT
  uid,
  kv1['A'] AS A_v1,
  kv1['B'] AS B_v1,
  kv2['A'] AS A_v2,
  kv2['B'] AS B_v2
FROM (
  SELECT uid,
      map_agg(key, value1) kv1,
      map_agg(key, value2) kv2
  FROM vtable
  GROUP BY uid
) 

Result:

uid | A_v1 | B_v1 | A_v2 | B_v2 
----+------+------+------+-----
 1  |  10  |  20  | 1000 | 2000
 2  |  11  |  21  | 1001 | 2001