# How to feed table with such a SQLite table?

**URL:** <https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415>\
**Category:** General\
**Created:** [7 April 2020 16:01 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415 "2020-04-07T16:01:36Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![tarahi](https://avatars.discourse-cdn.com/v4/letter/t/9de053/32.png) [@tarahi](https://discourse.nodered.org/u/tarahi)\
**Post date:** [7 April 2020 16:01 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/1 "2020-04-07T16:01:36Z")

</div>

Dear friends,  
I have a SQLite table with such data :  
 ![1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/8/8801d010b67b7965351d8a0297a281ffdfc9729c.jpeg)  
It stores sensor data .Column "device" stores sensor names and "val" stores their values.  
Now ,haw can I show such data in a table that shows all sensor and their values ?  
Thanks

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 April 2020 17:06 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/2 "2020-04-07T17:06:53Z")

</div>

Have you looked at the ui\_table node?

---

<div class="post-metadata">

**Author:** ![tarahi](https://avatars.discourse-cdn.com/v4/letter/t/9de053/32.png) [@tarahi](https://discourse.nodered.org/u/tarahi)\
**Post date:** [7 April 2020 18:15 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/3 "2020-04-07T18:15:17Z")

</div>

Yes Of course !  
I run it trough another example.But that SQLite table structure was different.There I have different sensors in different columns and feed ui\_table was simple :  
 ![3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/1/817f406744910a6e5ebadcbe03ddafc7c0f6c0a3.jpeg)  
But here I have one column for all sensor names,and another for their values :  
 ![1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/8/8801d010b67b7965351d8a0297a281ffdfc9729c.jpeg)  
My only problem is how I can get the query ...

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 April 2020 19:13 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/4 "2020-04-07T19:13:17Z")

</div>

Do you mean you want to show all the data ordered by the sensor?  
If so you will need to add that to your query. Do a google search using 'sqlite order by' to find the syntax.

---

<div class="post-metadata">

**Author:** ![tarahi](https://avatars.discourse-cdn.com/v4/letter/t/9de053/32.png) [@tarahi](https://discourse.nodered.org/u/tarahi)\
**Post date:** [7 April 2020 20:17 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/5 "2020-04-07T20:17:38Z")

</div>

I have searched a lot ,but no answer !  
the last result is :

```auto
SELECT id,
       (
		SELECT val
		FROM nplc
		WHERE device = "temperature3"
	)
	temp3,
	(
		SELECT val
		FROM nplc
		WHERE device = "temperature4"
	)
	temp4,
	(
		SELECT val
		FROM nplc
		WHERE device = "humidity3"
	)
	humi3,
	(
		SELECT val
		FROM nplc
		WHERE device = "humidity4"
	)
	humi4,
	thetime
FROM nplc
GROUP BY
    thetime
ORDER BY thetime DESC;

```

But the result :  
 ![2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/4/f437a9213e621bb4085343d4a757af4488c55f4e.jpeg)  
Every thing seems ok ,but the problem is that only 1st row is correct ,and it is repeating in other rows ! 🙄

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [8 April 2020 08:43 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/6 "2020-04-08T08:43:35Z")

</div>

What is the schema of the table?

---

<div class="post-metadata">

**Author:** ![tarahi](https://avatars.discourse-cdn.com/v4/letter/t/9de053/32.png) [@tarahi](https://discourse.nodered.org/u/tarahi)\
**Post date:** [8 April 2020 09:34 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/7 "2020-04-08T09:34:16Z")

</div>

I send the table with data look later in 3th post :  
[https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/3?u=tarahi](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/3)  
 ![1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/8/8801d010b67b7965351d8a0297a281ffdfc9729c.jpeg)  
And SQLite structure is :

```auto
CREATE TABLE nplc (id INTEGER PRIMARY KEY AUTOINCREMENT,device TEXT,val TEXT, thetime DATETIME DEFAULT (datetime('now','localtime')))

```

---

<div class="post-metadata">

**Author:** ![Christian-Me](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/christian-me/32/10774_2.png) [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)\
**Post date:** [8 April 2020 09:48 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/8 "2020-04-08T09:48:19Z")

</div>

You can use ui-table and feed the data using commands if all values you like to combine in one row have a common index (perhaps the timestamp)

Design a table with the columns you need: time, temp1, hum1, temp2, hum3 ....  
define the timestamp is your index field.

Then feed your ui-table with objects like this (pseudo code!)

```auto
msg.payload={
    "command":"updateOrAddData",
    "arguments": [{"time":timestamp,"temp1":value}],
    "returnPromise": false
};

```

ui-table can add or update new values to existing rows (identified by the index) or add new rows if the index is new / not existing.

But all depends on that you have a common field indicating which values goes into a row.

Certainly you can do this with an sql query (in which I'm not an expert) but you will again need a value indicating which values goes in one row. As I see your data it don`t look like you have that. All values look like they are written as they arrive.

If you like to combine sensors with there latest value the sensorName is your index.

Perhaps you can visualize how your table should look like in the end.

---

<div class="post-metadata">

**Author:** ![tarahi](https://avatars.discourse-cdn.com/v4/letter/t/9de053/32.png) [@tarahi](https://discourse.nodered.org/u/tarahi)\
**Post date:** [8 April 2020 09:57 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/9 "2020-04-08T09:57:29Z")

</div>

Dear Friend,  
As I told earlier ,I have made a ui\_table with such a SQLite table that you recommend.  
Now the main problem is ,how to get a suitable query for this kind of table :  
 ![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/8/8801d010b67b7965351d8a0297a281ffdfc9729c.jpeg)

---

<div class="post-metadata">

**Author:** ![Christian-Me](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/christian-me/32/10774_2.png) [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)\
**Post date:** [8 April 2020 10:06 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/10 "2020-04-08T10:06:14Z")

</div>

Sorry I can`t help you with SQL. I misunderstood your perhaps as I thought you would like to combine data in a different way. Like putting the devices in columns and the rows are defined by the timestamps (because this is possible by ui-table itself when you receive your data as shown in your screenshot.

| time | temp1 | hum1 | temp2 | hum2 |
| --- | --- | --- | --- | --- |
| 20:35 | 28.0 | 22% | 28.3 | 27.6% |
| 20:36 | 29.0 | 25% | 28.4 | 27.6% |

---

<div class="post-metadata">

**Author:** ![tarahi](https://avatars.discourse-cdn.com/v4/letter/t/9de053/32.png) [@tarahi](https://discourse.nodered.org/u/tarahi)\
**Post date:** [8 April 2020 10:14 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/11 "2020-04-08T10:14:03Z")

</div>

Right,  
Let me ask my Question in another form ,  
How can I convert my sqlite table in this form :  
 ![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/8/8/8801d010b67b7965351d8a0297a281ffdfc9729c.jpeg)  
to this :  
 ![](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/4/f437a9213e621bb4085343d4a757af4488c55f4e.jpeg)  
never mind with a single query or combine four queries like this for each time :

```auto
SELECT val FROM nplc	WHERE device = "temperature3";

```

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 April 2020 10:15 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/12 "2020-04-08T10:15:08Z")

</div>

In the database you have mulitple rows with the same timestamp, but you want them on the same row in the ui\_table. I am not sure whether you can do that with SQL, but as @Christian-Me says that isn't a question for this forum.

If I had this problem then I would write some javascript in a function node to loop through the rows, extracting the data in the appropriate form for the table.

---

<div class="post-metadata">

**Author:** ![Brian5600](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/brian5600/32/94271_2.png) [@Brian5600](https://discourse.nodered.org/u/Brian5600)\
**Post date:** [8 April 2020 11:17 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/13 "2020-04-08T11:17:12Z")

</div>

Hello,  
How would You like to merge the rows?  
If look at the first 4 rows, there is 2 different timestamps. If You aggregate on that, it will give 2 rows. 1 with data from id 1, and 1 with data from id 2-4.  
There need to be a key in each row, to combine the rows. It might be thetime, or something else.  
You can round the timestamp to a key, but then You risk the same key in 1 - more than 4 rows, with data from different sensors. Will You then have max, min, average if there is multiple values from one sensor.  
If a sensor have not stored a value, what will You then show? Nothing (null), the last, or?  
I think it can be done with sql. But I think You should take f.ex. 8 or 12 rows, and show manually, row for row, what You will show in the table You convert data to. Then You Can build You logic, which else might contain some randomness, or not what You want.  
It might also be an idear only to convert the amount of data You want in the table. With 16 rows, no problem, but if You have years, will You still show all data, or only the last hour or day.  
Best regards  
Brian

---

<div class="post-metadata">

**Author:** ![Christian-Me](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/christian-me/32/10774_2.png) [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)\
**Post date:** [8 April 2020 11:42 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/14 "2020-04-08T11:42:34Z")

</div>

I could think about this: Make the table a resolution of 10sec (cut off all the "noise")

```auto
timestamp=Math.floor(msg.timestamp/10000)*10000; //60000 for one minute

```

and then feed the data into ui-table as described before.

You will get all your data per 10 second interval (if any arrives) aggregated in one row, showing always the recent one if more than one arrives in this period.

And by the way ... perhaps connect your sensor directly to Node-RED and forward then to your database

---

<div class="post-metadata">

**Author:** ![tarahi](https://avatars.discourse-cdn.com/v4/letter/t/9de053/32.png) [@tarahi](https://discourse.nodered.org/u/tarahi)\
**Post date:** [8 April 2020 16:51 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/15 "2020-04-08T16:51:23Z")

</div>

Dear friends,  
[Colin](https://discourse.nodered.org/u/Colin)  
[zenofmud](https://discourse.nodered.org/u/zenofmud)  
[Brian5600](https://discourse.nodered.org/u/Brian5600)  
[Christian-Me](https://discourse.nodered.org/u/Christian-Me)  
Thanks for yor quick responses,Here is one of the best forums I've ever seen.Quick response with New elegant forum look ❤  
First I will try to find the SQLite code ,But if impossible,2nd ,I will try to write a code to specify and join sensor data according to their times.

---

<div class="post-metadata">

**Author:** ![Christian-Me](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/christian-me/32/10774_2.png) [@Christian-Me](https://discourse.nodered.org/u/Christian-Me)\
**Post date:** [8 April 2020 19:51 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/16 "2020-04-08T19:51:27Z")

</div>

You might already using one of these but I would use one of the many query builder tools to interactively build and debug your sql query (like this one / just googled it: [https://github.com/sqlitebrowser/sqlitebrowser](https://github.com/sqlitebrowser/sqlitebrowser))

---

<div class="post-metadata">

**Author:** ![tarahi](https://avatars.discourse-cdn.com/v4/letter/t/9de053/32.png) [@tarahi](https://discourse.nodered.org/u/tarahi)\
**Post date:** [9 April 2020 07:27 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/17 "2020-04-09T07:27:31Z")

</div>

I find similar process in :  
[Pivot — Rows to Columns](https://modern-sql.com/use-case/pivot)  
[How to pivot in SQLite or i.e. select in wide format a table stored in long format?](https://stackoverflow.com/questions/1237068/how-to-pivot-in-sqlite-or-i-e-select-in-wide-format-a-table-stored-in-long-form/1237082#1237082)

---

<div class="post-metadata">

**Author:** ![Brian5600](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/brian5600/32/94271_2.png) [@Brian5600](https://discourse.nodered.org/u/Brian5600)\
**Post date:** [9 April 2020 08:38 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/18 "2020-04-09T08:38:13Z")

</div>

Hello Tarahi  
Yes, thats nice. The second link, have some excamples, which can be copied right away.  
Best regards  
Brian

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [8 June 2020 08:38 UTC](https://discourse.nodered.org/t/how-to-feed-table-with-such-a-sqlite-table/24415/19 "2020-06-08T08:38:19Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.