How to Quickly Flatten a SQL Table

Question

I'm using Presto. If I have a table like:

ID    CATEGORY     VALUE
1      a           ...
1      b
1      c
2      a
2      b
3      b 
3      d
3      e
3      f

How would you convert to the below without writing a case statement for each combination?

ID      A     B    C    D    E    F
1
2
3

Answer 1

I've never used Presto and the documentation seems pretty thin, but based on this article it looks like you could do

SELECT
  id,
  kv['A'] AS A,
  kv['B'] AS B,
  kv['C'] AS C,
  kv['D'] AS D,
  kv['E'] AS E,
  kv['F'] AS F
FROM (
  SELECT id, map_agg(category, value) kv
  FROM vtable
  GROUP BY id
) t

Although I'd recommend doing this in the display layer if possible since you have to specify the columns. Most reporting tools and UI grids support some sort of dynamic pivoting that will create columns based on the source data.