Hi, I’ve set up a query that runs correctly on our Postresql DB but when I paste it into MetaBase’s SQL editor it pivots data in a completely wrong way.
Our data structure is very easy: we have a column for regions, one for months and another one with earnings that I’m summing up. Using crosstab() function, it should plot regions in the first column, months as column headers and the overall earnings in the inner cells. Everything ordered by the highest earnings in the last full month.
Unfortunately in MetaBase instead of having months as column headers it plots random values, and so does in the inner cells.
It would be easier by using MB pivot option and a much simpler query, but then again I can’t order by DESC/ASC in the chosen column.
Anyone has any idea on why? Thanks!
SELECT * FROM crosstab(' SELECT regione::text, to_char(mese, ''YYYY/MM''), sum(incasso_puc)::numeric as incasso FROM view_regional_history WHERE mese between ''2016-01-01'' and ''2017-01-01'' GROUP BY regione, mese ORDER BY regione DESC ', ' SELECT DISTINCT to_char(mese, ''YYYY/MM'') FROM view_regional_history WHERE mese between ''2016-01-01'' and ''2017-01-01'' ' ) AS ct(regione text, "2016/01" numeric, "2016/02" numeric, "2016/03" numeric, "2016/04" numeric, "2016/05" numeric, "2016/06" numeric, "2016/07" numeric, "2016/08" numeric, "2016/09" numeric, "2016/10" numeric, "2016/11" numeric, "2016/12" numeric, "2017/01" numeric ) ORDER BY "2016/12" DESC NULLS LAST;