How to Rotate Rows to Columns with Presto

Sometimes called pivoting, here is one example of how to switch columns to rows via rotation 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:

ab
9–
–8
7–

To rotate from rows to columns we will add an id to make aggregation easy. We will name the output columns a and b for the Presto key, 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.

Want more Presto tips & tricks? Sign up for our Presto community newsletter.

Presto community newsletter