Turn Presto columns to rows via rotation
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 bFROM table_a GROUP BY id