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,260 points
8,285 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
• 58,020 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
11,887 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,220 points
8,253 views
+1 vote
1 answer
0 votes
1 answer
0 votes
0 answers

How to get the record count of all tables in athena aws?

I'm trying to figure out how to ...READ MORE

Dec 13, 2022 in AWS by Ashwini
• 5,430 points
2,494 views
0 votes
1 answer

AWS Redshift failed to make a valid plan when trying to run a complicated query

-The Original query was an Oracle query ...READ MORE

answered Oct 3, 2018 in AWS by Priyaj
• 58,020 points
1,229 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
• 58,020 points
1,662 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,670 points
4,959 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP