Search code examples
sqlaws-glueamazon-athenaunpivot

Unpivot Columns inside of Amazon Athena without hardcoding

I am writing a query inside of AWS Athena. The Origianl Table is something like:

employee|manager1|manager2|manager3|... | manager10
   12345|A .     |B .     |C .     |... | (null)
   54321|I .     |II .    |III .   |... | X

And the result should look like:

employee | manager
12345 .  | A
12345 .  | B
12345 .  | C
54321    | I
54321 .  | II
54321 .  |...
54321 .  | X

I tried to use unpivot inside of Athena but got some syntax errors. Also, with unpivot it means i need to hardcoding all these columns.

This is my first time with Athena and I don't know if Athena can achieve it or not. And if yes, what's the correct way?

Solution

  • I just did an unpivot in Athena with arrays. My case didn't have a variable number of columns so you may need to adjust for that, but this should at least work to give you an unpivot:

    WITH dataset AS (
  SELECT
    employee,
    ARRAY[manager1, manager2, manager3] AS managers
  FROM
    (SELECT 'A' AS employee, '1' AS manager1, '2' AS manager2, '3' AS manager3)
)
SELECT employee, manager FROM dataset
CROSS JOIN UNNEST(managers) as t(manager)

    This will give you the output:

    code results

    Check the documentation for more info and examples: https://docs.aws.amazon.com/athena/latest/ug/creating-arrays.html