Question 330765 in PostgreSQL Question posted in
The official documentation can be found here.

Timescale/PostgreSQL – pivot timeseries data

I have the following table, containing timeseries data. All datapoints of one "dataset" have the same timestamp and value "const". The following example shows two of these "datasets".

+-----------+----------+----------+-------------+
| timestamp |  const   |   name   |    value    |
+-----------+----------+----------+-------------+
|    t0     |    A     |   name1  |   value0_1  |
+-----------+----------+----------+-------------+
|    t0     |    A     |   name2  |   value0_2  |
+-----------+----------+----------+-------------+
|    t0     |    A     |   name3  |   value0_3  |
+-----------+----------+----------+-------------+
|    t0     |    A     |   name4  |   value0_4  |
+-----------+----------+----------+-------------+
|    t1     |    B     |   name1  |   value1_1  |
+-----------+----------+----------+-------------+
|    t1     |    B     |   name2  |   value1_2  |
+-----------+----------+----------+-------------+
|    t1     |    B     |   name3  |   value1_3  |
+-----------+----------+----------+-------------+
|    t1     |    B     |   name4  |   value1_4  |
+-----------+----------+----------+-------------+

Now, I want to pivot this datasets from rows to columns. This means in each row one complete dataset.

+-----------+----------+----------+----------+----------+----------+
| timestamp |  const   |   name1  |   name2  |   name3  |   name4  |
+-----------+----------+----------+----------+----------+----------+
|    t0     |    A     | value0_1 | value0_2 | value0_3 | value0_4 |
+-----------+----------+----------+----------+----------+----------+
|    t1     |    B     | value1_1 | value1_2 | value1_3 | value1_3 |
+-----------+----------+----------+----------+----------+----------+

Any suggestions how this could be solved in an elegant Timescale/PostgreSQL query?

Thanks in advance!

nico

Tags:

2

Answers


  1. 0

    You can select distinct names and generate a text by concatting query clauses and parts with the help of string_agg that glues together the names into dynamic field lists on the one hand and left joins on the other. The following query is such an example, but it was not tested, so it may contain typos or mistakes on my part.

    select concat(
    'select timestamp, const, ',
    string_agg(concat('table_', name, '.' name), ','),
    ' ',
    'from yourtable yt ',
    string_agg(concat(
        'left join table_',
        name,
        ' ',
        'on ',
        'yt.timestamp = table_', name, '.timestamp and ',
        'yt.const = table_', name, '.const and ',
        'table_', name, '.name = '', name, '''
    ), ' ')
)
from (select distinct name from yourtable) t;
    Login or Signup to reply.
  2. 0

    As you may not know upfront the number of values per timestamp/const pair it’s more relevant to store the list of values into an array rather than in separate columns. Then it’s simple as that –

    select "timestamp", const, 
       array_agg(distinct "value") as names
from the_table
group by "timestamp", const;

    Demo
    You may use jsonb_agg instead of array_agg to have the list as a JSON array if this is more relevant to your case.

    If you need the contents of column "name" in the result then the result may be a JSON array of objects:

    select "timestamp", const, 
       jsonb_agg(jsonb_build_object(name, "value")) as names
from the_table
group by "timestamp", const;

    Demo
    Unrelated but using reserved words for names is not a good idea.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search