Combine same named columns from different tables *without* merging the columns-CodePudding

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!

CodePudding user response：

You could aggregate meter data into a CSV:

SELECT
  "0.9.2" AS datestamp,
  string_agg("1.8.0", ',') AS meterdata
FROM public.meter354123
GROUP BY "0.9.2"

Or to get an actual array:

SELECT
  "0.9.2" AS datestamp,
  array_agg("1.8.0") AS meterdata
FROM public.meter354123
GROUP BY "0.9.2"

CodePudding user response：

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

CREATE VIEW meter354123 AS SELECT meterdata."0.0.0",
  meterdata."0.9.1",
  meterdata."0.9.2",
  meterdata."1.8.0"
  FROM meterdata
  WHERE meterdata."0.0.0" = 354123::numeric AND meterdata."0.9.1" = 0::numeric
  ORDER BY meterdata."0.0.0", meterdata."0.9.2" DESC
  LIMIT 12;
CREATE VIEW meter354124 AS SELECT meterdata."0.0.0",
  meterdata."0.9.1",
  meterdata."0.9.2",
  meterdata."1.8.0"
  FROM meterdata
  WHERE meterdata."0.0.0" = 354124::numeric AND meterdata."0.9.1" = 0::numeric
  ORDER BY meterdata."0.0.0", meterdata."0.9.2" DESC
  LIMIT 12;
CREATE VIEW meter354127 AS SELECT meterdata."0.0.0",
  meterdata."0.9.1",
  meterdata."0.9.2",
  meterdata."1.8.0"
  FROM meterdata
  WHERE meterdata."0.0.0" = 354127::numeric AND meterdata."0.9.1" = 0::numeric
  ORDER BY meterdata."0.0.0", meterdata."0.9.2" DESC
  LIMIT 12;
CREATE VIEW "meter354123_1.8.0" AS SELECT public.meter354123."0.9.2" AS datestamp,
  public.meter354123."1.8.0" AS meter354123
  FROM public.meter354123
  ORDER BY datestamp DESC
  LIMIT 12;
CREATE VIEW "meter354124_1.8.0" AS SELECT public.meter354124."0.9.2" AS datestamp,
  public.meter354124."1.8.0" AS meter354124
  FROM public.meter354124
  ORDER BY datestamp DESC
  LIMIT 12;
CREATE VIEW "meter354127_1.8.0" AS SELECT public.meter354127."0.9.2" AS datestamp,
  public.meter354127."1.8.0" AS meter354127
  FROM public.meter354127
  ORDER BY datestamp DESC
  LIMIT 12;
SELECT "meter354123_1.8.0".datestamp,
  "meter354123_1.8.0".meter354123,
  "meter354124_1.8.0".meter354124,
  "meter354127_1.8.0".meter354127
  FROM "meter354123_1.8.0"
  JOIN "meter354124_1.8.0" ON "meter354123_1.8.0".datestamp = "meter354124_1.8.0".datestamp
  JOIN "meter354127_1.8.0" ON "meter354123_1.8.0".datestamp = "meter354127_1.8.0".datestamp;

which results in:

 datestamp | meter354123 | meter354124 | meter354127
----------- ------------- ------------- -------------
   1220301 |    11055.66 |     5403.16 |    88556.23
   1220201 |    11054.64 |     5399.47 |    88195.41
   1220101 |    11053.33 |     5395.27 |    87799.84

I don't know if there is a more efficient/elegant solution, but at least this gives the wanted result.