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,140 points
518 views

1 answer to this question.

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
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
• 56,140 points

Related Questions In AWS

0 votes
1 answer

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

This is a known deficiency. The best ...READ MORE

answered Sep 4, 2018 in AWS by Archana
• 4,090 points
602 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 in AWS by Edureka
• 4,180 points
257 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,300 points
576 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,520 points
236 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
• 56,140 points
49 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,240 points
694 views

© 2018 Brain4ce Education Solutions Pvt. Ltd. All rights Reserved.
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.