There are few answers around how to pivot data using Athena-SQL. Their common drawback is that one has to know in advance what are all values of the column in the input data that will become the columns in the pivoted version.
For example, assume, the input data looks like:
User Fact Value
------- ---- ------
102 1 a
101 1 b
102 2 c
103 3 d
...
and the expected pivot is:
User Fact_1 Fact_2 Fact_3
------- ------ ------ ------
101 b NULL NULL
102 a c NULL
103 NULL NULL d
...
Now the key challenge is that the number of unique values in Fact is high (say ~1k). How can I build the pivot table without manually specifying all the unique values of Fact (i.e. the columns in the result)?
For a limited number of columns, as suggested here, I could do something like:
SELECT
User,
MAX(CASE WHEN Fact = '1' THEN Value END) AS Fact_1,
MAX(CASE WHEN Fact = '2' THEN Value END) AS Fact_2
FROM my_yable
GROUP BY User
It is also simple to create a table with the unique Fact values, but I don't know how to use it...