I’ve got a table to store collected data from several energy meters, then I created some views to show data from specific meters only. Now I want to combine those views for an overview of only interesting data.
As far as I understood from reading other questions, (where my question here could be a possible duplicate?) JOIN would be what I need and that creates new columns, but the columns with the values of the meters get merged. I guess this is because the columns with the interesting values have all the exact same name, but that is not what I want. I want the colums with the interesting values (named "1.8.0") not merged but in seperate columns as they are in the views, just next to each other for a better overview.
To shorten the post I created following example to show my problem:
http://sqlfiddle.com/#!17/a886d/31 (and maybe also http://sqlfiddle.com/#!17/a886d/30 )
The related query:
SELECT public.meter354123."0.9.2" AS datestamp,
public.meter354123."1.8.0" AS meter354123
FROM public.meter354123
FULL JOIN public.meter354124 ON public.meter354123."1.8.0" = public.meter354124."1.8.0";
For some reason I do not understand yet, the JOIN does not work for me as I would expect. If I JOIN ON the values (column "1.8.0") I get NULL rows, if I JOIN ON the datestamps (column "0.9.2"), one column is missing completely in the result.
(if it is meaningful, feel free to edit the code from the fiddle here into the question, I thought it would be too much code to paste here and I don’t know how to explain my issue more simpler)
In the end I would like to have a result like:
| datestamp (=col "0.9.2") | meterdata1 (=col "1.8.0") | meterdata2 (=col "1.8.0") | etc...
| 1220101 | value1 | value1 | ...
| 1220201 | value2 | value2 | ...
| 1220301 | value3 | value3 | ...
Maybe the intermediate views are not necessary at all and it is even possible to pull off this result from the original table without going through those views?
I’m not a database expert so I went with my current knowledge to accomplish that.
Thank you very much for looking into this and for any hints!
2
Answers
Thank you for looking into this, I could "solve" this by using another several intermediate views and then simple JOIN-ing those views as following:
see fiddle: http://sqlfiddle.com/#!17/a886d/40
which results in:
I don't know if there is a more efficient/elegant solution, but at least this gives the wanted result.
You could aggregate meter data into a CSV:
Or to get an actual array: