Sorting the month number properly in pivot table

Question from our customer:
When using the pivot table to pivot month, the month number starts with 1 and then follow by 10 instead of 2, 3, 4,… Why is this the case and how can I fix this?

Answer
This is because currently, Holistics pivot table doesn’t recognize number as its field, thus when sorting the data, 10 will comes before 2.

While we’re working on a patch to fix this properly, one workaround is to pad the single-digit month with padding zero so that it becomes 01, 02, 03, … you can do this by modifying your query to extract month number to:

  -- postgresql
  select lpad(month::varchar, 2, '0') as month
  -- mysql
  select lpad(cast(month as varchar), 2, '0') as month

And you will get the correct answer:


p/s: FYI, the full query to reproduce the data:

with M as (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)),
C as (values ('Books'), ('Laptop'), ('Phones'))
select
  C.column1 as product,
  lpad(M.column1::varchar, 2, '0') as month,
  random() * 10000 as sales
from M cross join C