我是 AWS Athena 的新手，并试图将一些行转换为列，类似于此 StackOverflow 帖子.

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

但是，当我尝试时:

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

我收到错误: mismatched input '(' Expecting {',', ')'}(服务:amazonathena；状态代码:400；错误代码:invalidrequestexception

有人知道如何在 AWS Athena 中实现我想要实现的目标吗?

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

推荐答案

扩展@kadrach 的答案.假设这样的表

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

单列 PIVOT 是这样工作的

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
) 

结果:

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

多列 PIVOT 是这样工作的

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
) 

结果:

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