Обсуждение: transpose time-series columnar data
Hi all,
I got a view that returns values in the following format
timestamp | name | value
---------------------------------
ts1 name1 value11
ts1 name2 value12
ts1 name3 value13
ts2 name1 value21
ts2 name2 value22
ts2 name3 value23
ts3 name1 value31
ts3 name2 value32
ts3 name3 value33
I need to transpose and return one row for a single timestamp and one column for every name (fixed number of names), something like this
timestamp | name1 | name2 | name 3
-----------------------------------------------------------------------
ts1 value11 value12 value13
ts2 value21 value22 value23
ts3 value31 value32 value33
Any help would be appreciated
Thanks and sorry for my english
--
Edu
On Tue, Aug 17, 2021 at 11:29 AM Edu Gargiulo <egargiulo@gmail.com> wrote:
I need to transpose and return one row for a single timestamp and one column for every name (fixed number of names), something like this
or
select ..., max(value) filter (where name = 'name1') as name1, max(value) filter (where name = 'name2') as name2, etc from tbl group by ...
David J.
As suggested by David I would use Tablefunc.
This query should work.
select * from crosstab (
'select timestamp, name, value from originaltable',
'select distinct name from originaltable order by name'
)
as transposedtable (timestamp varchar, name1 varchar, name2 varchar, name3 varchar)
'select timestamp, name, value from originaltable',
'select distinct name from originaltable order by name'
)
as transposedtable (timestamp varchar, name1 varchar, name2 varchar, name3 varchar)
Regards,
Walter
El mar, 17 de ago. de 2021 a la(s) 14:42, David G. Johnston (david.g.johnston@gmail.com) escribió:
On Tue, Aug 17, 2021 at 11:29 AM Edu Gargiulo <egargiulo@gmail.com> wrote:I need to transpose and return one row for a single timestamp and one column for every name (fixed number of names), something like thisorselect ..., max(value) filter (where name = 'name1') as name1, max(value) filter (where name = 'name2') as name2, etc from tbl group by ...David J.