Turn Presto columns to rows via rotation

Question

This is the desired input and desired output. I'm unfamiliar with the terms used in SQL or Presto and the documentation seems to point to using map_agg but I think the problem here is dynamically creating columns but was curious if this is possible where the a, b, ... columns are known and finite.

I'd be great to know the proper function for this in SQL or Presto and of course if this is possible. Preferably in a way that doesn't involve manually adding a clause per desired row => column. There must be a way to do this automatically or by providing a list of values to filter rows that get converted to headers (As in how 'a' below gets moved to being a column title)

table_a:

id | key | value

 0 |   'a'  |   1
 1 |   'b'  |   2

Then becomes desired:

id | 'a' | 'b' 
 0    1    2

The closest I can get is to use map_agg to get a set of key: values which can be pulled one at a time in the output. However the desired solution would be to not have to explicitly list every key I want outputted in the end and instead explode or roll out all keys of kvs:

with subquery_A as (
  select 
    id,   
    map_agg(A.key, A.value) as "kvs"
  from A as a
  group by 1
)

select 
  sub_a.id,
  sub_a.kvs['a'],
  sub_a.kvs['b']

from subquery_A as sub_a

Answer 1

In pretty much all database servers, queries return a fixed set of columns. The RDBMS needs to know which columns it will need to output in order to properly process the query.

So, one way or another, you usually need to explcitely define the output columns.

Your solution seems to work fine on Presto DB. Just in case, you want to compare it to something else, here is a typical solution in standard SQL, that uses conditional aggregation to pivot the data over a (fixed) set of columns. It does not uses a CTE, and most RDBMS support this syntax.

SELECT
    id,
    MAX(CASE WHEN key = 'a' THEN value END) AS a
    MAX(CASE WHEN key = 'b' THEN value END) AS b
FROM table_a 
GROUP BY id

Answer 2

Apparently PIVOT function has not been implemented for Prestodb.

You could do like below. It is similar to your solution but maybe a little cleaner:

SELECT
  id,
  key['a'] AS A,
  key['b'] AS B
FROM (
  SELECT id, map_agg(key, value) key
  FROM table_a
  GROUP BY id
) temp