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,180 points
1,038 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
• 56,920 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 in AWS by athenauserz
1,460 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
774 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,350 points
1,026 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,560 points
434 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,920 points
90 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,280 points
1,026 views