I have a (postgresql) database table that is a list of views by date, page and user:
date,page,user
2023-01-01,home,user1
2023-01-01,home,user2
2023-01-02,foo,user3
2023-01-02,bar,user1
I want to create a stacked bar chart that is the count of views grouped by page, and stacked by user. I do not care about the date field (I will use it to filter for the last x days, but I do not need/want it in my chart).
Looking at this example suggests that if my data looked like below, everything would "Just Work":
page,user1,user2,user3
home,1,1,0
foo,0,0,1
bar,0,1,0
Dynamic pivot tables appear to be a nightmare in postgresql (and I think I can only run a single statement within grafana to get my data).
I therefore wanted to use the internal 'Transform' functionality of grafana. I should be able to group my table by page and user, and then use the rows to fields transformation.
HOWEVER. I'm failing miserably. The rows to fields transformation drops the "page" column so all I get is:
user1,user2,user3,
1,1,0
0,0,1
0,1,0
which is useless.
Is this possible? If so, how?
