Pivot rows into columns in AWS Athena?

0 votes

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?

Oct 29, 2018 in AWS by findingbugs
• 3,200 points
1,246 views

1 answer to this question.

0 votes

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
)
answered Oct 29, 2018 by Priyaj
• 57,490 points

Related Questions In AWS

0 votes
2 answers

How to skip headers when reading a CSV file in S3 and creating a table in AWS Athena?

Thanks for the answer. This should be clear ...READ MORE

answered Aug 14, 2019 in AWS by athenauserz
1,877 views
0 votes
1 answer

How to delete / drop multiple tables in AWS athena?

It is not possible to run multiple ...READ MORE

answered Jan 17, 2019 in AWS by Edureka
• 4,180 points
1,090 views
+5 votes
2 answers

Can we export/migrate users from AWS cognito, does it cause vendor lock-in?

Cognito actually has the capability to import ...READ MORE

answered Aug 1, 2018 in AWS by bug_seeker
• 15,390 points
1,226 views
0 votes
1 answer
0 votes
1 answer

Connect AWS Athena to an existing Hive Metastore

Athena works only with its own metastore ...READ MORE

answered Nov 16, 2018 in AWS by Jino
• 5,630 points
497 views
0 votes
2 answers

Receiving SMS from users and stores in AWS

As far as I know, receiving international ...READ MORE

answered Aug 21, 2018 in AWS by Priyaj
• 57,490 points
109 views
+1 vote
2 answers

How do I get my AWS Glue client in JAVA?

Hey, you've been using a correct code ...READ MORE

answered Apr 17, 2018 in AWS by Cloud gunner
• 4,620 points
1,186 views