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

What database software are you using? Many have a PIVOT function but the syntax varies quite a bit. Search your server software's documentation for PIVOT and see if that doesn't work for you. I'd also point out that _dynamic_ pivoting ig generally much easier in the display (report,app) layer. — D Stanley, Jun 09 '16 at 22:44
I had a quick look at the Presto doco here: https://prestodb.io/docs/current/ and couldn't see anything resembling pivot. Why don't you want to use case? Again this is best done in the display layer... one reason being of course is that in the display layer you are not required to know each column before it exists. — Nick.Mc, Jun 09 '16 at 22:50

score 4 · Answer 1 · answered Jun 09 '16 at 22:51

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.

score 0 · Answer 2 · answered May 23 '17 at 10:07

My 2 cents:

If you know "possible" values:

SELECT 
    m['web'] AS web,
    m['shopping'] AS shopping,
    m['news'] AS news,
    m['music'] AS music,
    m['images'] AS images,
    m['videos'] AS videos,
    m[''] AS empty 
FROM (
SELECT histogram(data_tab) AS m
FROM datahub
WHERE
    year = 2017
    AND month = 5
    AND day = 7
    AND name = 'search'
) searches

No PIVOT function (yet)!

How to Quickly Flatten a SQL Table

2 Answers2

Linked