SQL sensor with multiple columns and rows from MySQL

Development
1

Hi all,

I am a newbie here and am looking for ways that I could expose a SQL query that returns multiple rows and columns via Hass, preferably through the REST API. Basically, I have a custom database table that is populated with many data. I would like to aggregate those data (hence many columns) and present it somehow in a figure/plot in HADashboard.

I had a look at SQL sensor and it seems like it only accepts maximum one row and one column. I don’t want to create a sensor for each column/row pairs, and I cannot figure out how the SQL sensor will store anything in the attributes.

Any suggestion would be much appreciated.

Thanks!

Zhao

2

Have you found a solution?

3

I managed to do something similar to this in NodeRED

You can use the SQL node connected to a function node to process the data.

You can then send it to HA using MQTT or websocket node.

It would be nice to have a solution using only HA however.

4

Limit to 1 row and 1 column is very strict!
In my case, I want to get the current state and the last update of a door sensor to show like this:

Front Door (Close, Last Opened: Nov 6 4:00PM)

Now I ended up to show 2 lines:

Front Door Close
Front Door Last Opened Nov 6 4:00PM

10 sensors took 20 rows in my UI, so sad :frowning:

1 Like