How to split multiple rows in to single row but multiple columns in AWS athena

I think you should try to work with array_agg rather than map_agg as the key will be similar for the whole map:

array_agg(x) → array<[same as input]>
Returns an array created from the input x elements.

You should be then able to access array elements with role_claims[1] or element_at:

SELECT client,active,claim,role_polh,role_agnt,
element_at(role_claims,1) AS role_clams_1,
element_at(role_claims,2) AS role_claims_2
FROM (
  SELECT client,active,claim,role_polh,role_agnt,array_agg(role_clai) role_claims
  FROM "final_view"
  GROUP BY client,active,claim,role_polh,role_agnt
)  where claim = '00600000000015609'

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top