(un)pivot on Presto

20171104_2.md

Pivot

Query

SELECT
  uid,
  kv['c1'] AS c1,
  kv['c2'] AS c2,
  kv['c3'] AS c3
FROM (
  SELECT uid, map_agg(key, value) kv
  FROM vtable
  GROUP BY uid
) t

Result

uid  c1  c2  c3
---  --  --  --
101  11  12  13
102  21  22  23

Unpivot

Query

SELECT t1.uid, t2.key, t2.value
FROM htable t1
CROSS JOIN unnest (
  array['c1', 'c2', 'c3'],
  array[c1, c2, c3]
) t2 (key, value)

Result

uid  key  value
---  ---  -----
101   c1     11
101   c2     12
101   c3     13
102   c1     21
102   c2     22
102   c3     23

your dynamic pivot query:
SELECT uid, kv['c1'] AS c1, kv['c2'] AS c2, kv['c3'] AS c3 FROM ( SELECT uid, map_agg(key, value) kv FROM vtable GROUP BY uid ) t
to make the following part dynamic in query:
kv['c1'] AS c1, kv['c2'] AS c2, kv['c3'] AS c3.
I converted the above query as following:
SELECT uid, (select array_join(transform((select array_agg(distinct column) from vtable), x->format_element(x)), ',' )) FROM ( SELECT uid, map_agg(key, value) kv FROM vtable GROUP BY uid ) t
But the join part is returning string not executeable columns in query. Can anyone guide me what I'm doing wrong.

Just want to note that after at least an hour of trying to unpivot a Presto table, this gist provided a clearer explanation than any other official documentation or any StackOverflow.com responses. After reading through both examples, I realized what I was doing wrong in ~10 seconds.

Thanks for posting and may you live a long, happy life with many children and several homes scattered throughout the globe.

your dynamic pivot query:
SELECT uid, kv['c1'] AS c1, kv['c2'] AS c2, kv['c3'] AS c3 FROM ( SELECT uid, map_agg(key, value) kv FROM vtable GROUP BY uid ) t
to make the following part dynamic in query:
kv['c1'] AS c1, kv['c2'] AS c2, kv['c3'] AS c3.
I converted the above query as following:
SELECT uid, (select array_join(transform((select array_agg(distinct column) from vtable), x->format_element(x)), ',' )) FROM ( SELECT uid, map_agg(key, value) kv FROM vtable GROUP BY uid ) t
But the join part is returning string not executeable columns in query. Can anyone guide me what I'm doing wrong.

Did you find any solution for this one?

Just want to note that after at least an hour of trying to unpivot a Presto table, this gist provided a clearer explanation than any other official documentation or any StackOverflow.com responses. After reading through both examples, I realized what I was doing wrong in ~10 seconds.

Same here. I was wrecking my brains on how to unpivot data in Athena/Presto. This is still back-magic-juju-voodoo-y to me, but it works and I'm very grateful, as it saves me headaches in my downstream pipeline.