Using SQL queries, creating one column per each distinct value of columns used in Group By clause

Grafana Bar Chart Panel
1

  • What Grafana version and what operating system are you using?
    Grafana v9.5.3 (916d9793aa)

  • What are you trying to achieve?
    I am using Databricks clusters as data source. My query looks like this:
    SELECT time_col, status, sum(count) AS count
    FROM ,y_table
    WHERE $__timeFilter(time_col)
    GROUP BY time_col, status
    ORDER BY time_col, status

I want to have a stacked bar chart. Each stack has the sum(count) of different status stacked on top of each other for a given value of time_col.
I thought what I need is the count column pivoted based on values in status column.
Example: if status column has two distinct value: status_1 and status_2; I want to have such a result:

time_col count_status_1 count_status_2 
  ....        |           ......               |             .....

  • How are you trying to achieve it?
    I was thinking Grafana handles it. I had seen examples before. But I am not sure how they are done. I tried color by col option from Bar Chart Setting side panel.

  • What happened?
    Group by, by itself does not do the trick. Using color by col also didn’t help. I see different status values, but they are all treated as a single column and with the same color without being able to stack them.

  • Did you follow any online instructions? If so, what is the URL?
    No

2

Solved. I understood that to achieve what I was looking for, I just need to set a transform as Follows:

Transform → Prepare time series → Multi-frame time series

The SQL itself just needs to group by the column you want to split the time series by. Also, that column should exist in the group by clause.