# How to visualize multiple columns selected by multiple selection in a variable

**URL:** <https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756>\
**Category:** Table Panel\
**Tags:** transformations\
**Created:** [May 2, 2024, 7:12am UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756 "2024-05-02T07:12:10Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![fjanni](https://avatars.discourse-cdn.com/v4/letter/f/a88e4f/32.png) [@fjanni](https://community.grafana.com/u/fjanni)\
**Post date:** [May 2, 2024, 7:12am UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/1 "2024-05-02T07:12:10Z")

</div>

Grafana v10.2, mySQL, Table visualization.  
Query: There are 30 queries in which I calculate monthly consumption collected by 30 sensors.  
Task: I’d like to create a table visualization in which I can see the monthly consumption of selected sensors and their summary for each year and month like this: Year / Month / Sensor2 / Sesor14 / Total - which are match the filter.  
Process:  
Step1: Concatenate fields - to see each query results in one table  
Step2: Filter by name/Identifier: “._${Site}._|._Year._|._Month._” - Filter by one specific text or the whole name of sensor name which is selected in $Site variable  
Step3: Add field from calculation/Reduce row/select all numeric fields/Total - to calculate the summary of selected rows and to see it in the last column.

It works, if I select only one option from the drop-down list. If I select 2 or more, it will result nothing.  
How to handle multiple selection in regex where there is a “+” in between selected text and I want both to be considered separately.

---

<div class="post-metadata">

**Author:** ![grant2](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/grant2/32/42281_2.png) [@grant2](https://community.grafana.com/u/grant2)\
**Post date:** [May 2, 2024, 9:38am UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/2 "2024-05-02T09:38:51Z")

</div>

Hi @fjanni  
Can you share your query?

In SQL, when wanting to select multiple regions (New York, California, etc.), I do this:

```auto
SELECT * FROM "ElectricPowerOperations" WHERE "type" IN ('Demand') AND "region" IN (${region}) AND $__timeRange(time)

```

which looks like this when inspected in Grafana after executing:

```auto
SELECT * FROM "ElectricPowerOperations" WHERE "type" IN ('Demand') AND "region" IN ('California','Florida','New York','Tennessee') AND time >= '2024-04-17T09:38:11Z' AND time <= '2024-05-02T09:38:11Z'

```

---

<div class="post-metadata">

**Author:** ![fjanni](https://avatars.discourse-cdn.com/v4/letter/f/a88e4f/32.png) [@fjanni](https://community.grafana.com/u/fjanni)\
**Post date:** [May 2, 2024, 3:54pm UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/3 "2024-05-02T15:54:28Z")

</div>

Hi, this is one query from 30:

```auto
With 
T0 as (
      With T as (
      SELECT $Year AS year, m AS month, null AS Consumption
      FROM (
      SELECT 1 AS m UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
      SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
      SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
      SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
      ) AS months
      )
Select Year, Month, Consumption from T
Where year in ($Year) and month in ($Month)
),

T1 as (
      With T AS (
            Select
            Year (Zeit) as Year,
            Month(Zeit) as Month,
            Zeit as Time, 
            Zaehlerstand - LAG(Zaehlerstand) OVER (ORDER BY Zeit) as Consumption
            FROM emspressiggas.002_prg_naturalgas_schlossereiheizung
      )
select year, month,
CASE
    when '$Energy' = 'm3' Then Sum(consumption)
    when '$Energy' = 'nm3' Then Sum(Consumption)*(select gas_faktor from ems.gas_faktor_nm3 where db_spalte='002_prg_naturalgas_schlossereiheizung')
    when '$Energy' = 'kWh' Then Sum(Consumption)*(select gas_faktor from ems.gas_faktor_nm3 where db_spalte='002_prg_naturalgas_schlossereiheizung')*(select brennwert_erdgas_pressig from ems.gas_zaehlerstaende where jahr = year and monat = month) 
    else null
END as Consumption 
from T 
Where year in ($Year) and month in ($Month)
Group by Year,Month
Order by Time
)

select 
T1.Consumption as '002_prg_naturalgas_schlossereiheizung' 

from T0 

left outer join T1 on T1.year=T0.year and T1.month=T0.month

```

In the rest of queries table name is changing.

Here is the result when I select columns which contains “naturalgas”, “prg” and “tnv”

 ![image](https://us1.discourse-cdn.com/grafana/original/3X/c/5/c51b9fcffed841af99b9f644827016f6f6885673.png)

But when I select multiple value for example “prg + hdf” in Site variable, it doesnt work.  
Or when I want to exclude one sensor by selecting by multiple selection all necessary sensor except one, it doesnt work.

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [May 2, 2024, 6:08pm UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/4 "2024-05-02T18:08:59Z")

</div>

where is the site variable used in your query. it is not clear

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [May 2, 2024, 6:11pm UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/5 "2024-05-02T18:11:06Z")

</div>

> [@fjanni](#):
>
> There are 30 queries in which I calculate monthly consumption collected by 30 sensors

this is not scalable approach. what if tomorrow, you grow to 150 sensors, are you going to create 150 queries?

---

<div class="post-metadata">

**Author:** ![fjanni](https://avatars.discourse-cdn.com/v4/letter/f/a88e4f/32.png) [@fjanni](https://community.grafana.com/u/fjanni)\
**Post date:** [May 3, 2024, 7:10am UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/6 "2024-05-03T07:10:29Z")

</div>

You are right, would you have an idea how to make it scalable? A DO … WHILE would be great but how to do it if each sensor data is in a different table. I can list the tables with SHOW TABLES but how to run this query in each table and show the results in one table chart?

The Site variable is setup at the variable with Custom type and value is “prg,hdf”  
And filter is setup at Transform data / Filter by name / Identifier: ._${Site}._|._Year._|._Month._  
When only prg or hdf is selected it is ok, but in case of multiple selection “prg + hdf” it doesn’t work, I can see nothing.

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [May 3, 2024, 11:14am UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/7 "2024-05-03T11:14:41Z")

</div>

from a db design pov why do you have a separate table per sensor?

schema wise are all of these tables identical ?

---

<div class="post-metadata">

**Author:** ![fjanni](https://avatars.discourse-cdn.com/v4/letter/f/a88e4f/32.png) [@fjanni](https://community.grafana.com/u/fjanni)\
**Post date:** [May 6, 2024, 10:12am UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/8 "2024-05-06T10:12:44Z")

</div>

Separate table per sensor: it is company specific, no way to change.  
Schema: all of the tables are identical as for the schema

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [May 8, 2024, 1:07pm UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/9 "2024-05-08T13:07:01Z")

</div>

ok if tables same can you create a view that uses Union to tie them all up? one of the fields should be sensorname as metric

---

<div class="post-metadata">

**Author:** ![fjanni](https://avatars.discourse-cdn.com/v4/letter/f/a88e4f/32.png) [@fjanni](https://community.grafana.com/u/fjanni)\
**Post date:** [May 8, 2024, 3:15pm UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/10 "2024-05-08T15:15:29Z")

</div>

Thanks. I already tried it with UNION and LEFT OUTER JOIN and both were ok.  
But the problem is that both of them are static query and if there is new sensor I have to update the query, by adding a new subquery with this new table name (FROM).  
Your suggestion was to simplify it and I also looking for the solution how to make an automatic update.  
For example if a new sensor table is created → sensor list is updated when in a query table list is run SHOW TABLES → and sensor data is considered by adding a new colums on the table dashboard. This is what I would like to do and I would need help how to do this.  
I understand your idea, to put sensor name (table name) as a metric. This is good idea. How could i do it taking into consideration the aboves?

---

<div class="post-metadata">

**Author:** ![yosiasz](https://sea2.discourse-cdn.com/grafana/user_avatar/community.grafana.com/yosiasz/32/45847_2.png) [@yosiasz](https://community.grafana.com/u/yosiasz)\
**Post date:** [May 8, 2024, 3:26pm UTC](https://community.grafana.com/t/how-to-visualize-multiple-columns-selected-by-multiple-selection-in-a-variable/120756/12 "2024-05-08T15:26:17Z")

</div>

Closed on May 8, 2024, 3:26pm UTC