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
uid c1 c2 c3
--- -- -- --
101 11 12 13
102 21 22 23
SELECT t1.uid, t2.key, t2.value
FROM htable t1
CROSS JOIN unnest (
array['c1', 'c2', 'c3'],
array[c1, c2, c3]
) t2 (key, value)
uid key value
--- --- -----
101 c1 11
101 c2 12
101 c3 13
102 c1 21
102 c2 22
102 c3 23
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.