Pivot the result of a SQL query

1

Is it possible to do a pivot table on the result of a SQL query? For example, I create a new question using the UI on the sample dataset to get from Orders “Count of rows by Product > Category and User > State”. I format as a table and I get a nice pivot, with one column per product category.

However, if I try to do this in SQL, I just get the three columns directly from my query.

select category, state, count(*)
from orders o join products p on p.id = o.product_id
join people pp on o.user_id = p.id
group by category,state
2

Not currently. You’d need to use crosstab (in Postgres) or the equivalent for now.

3

Opened an issue at https://github.com/metabase/metabase/issues/2415 .

We’ll discuss and figure out when/how to do this. If you’d care to chime in to get the conversation started that would be great!

4

agreed - pivot table reporting is really necessary

1 Like