How do you rotate rows to columns with Presto?
Sometimes called pivoting, here is one example of how to rotate row data with Presto.
Suppose we have rows of data like this:
'a', 9 'b', 8 'a', 7
We want to pivot this data so that all the ‘a’ row values are arranged in one column, and all the ‘b’ row values are in a second column like this:
To rotate from rows to columns we will add an id to make aggregation easy. We will name the output columns a and b, and we’ll include the id in our result set. This is how we do the rotation in Presto, using VALUES() to supply the test data, and simple conditional CASE WHEN END logic:
presto:default> SELECT id , MAX(CASE WHEN key = 'a' THEN value END) AS a , MAX(CASE WHEN key = 'b' THEN value END) AS b FROM (VALUES (1, 'a', 9), (2, 'b', 8), (3, 'a', 7 )) as test_data (id, key, value) GROUP BY id ORDER BY id; id | a | b ----+------+------ 1 | 9 | NULL 2 | NULL | 8 3 | 7 | NULL (3 rows)
There are other SQL options for transforming (pivoting) rows into columns – you can use the map_agg function for example.
The code sample and description here should help when you need to rotate data from rows to columns using Presto.
If you want to get up and running with Presto, Ahana Cloud’s cloud managed service built for AWS is the easiest way to do that. See our docs for more details.