下表如何在PostgreSQL中将行转换为列 [英] How to convert row into column in PostgreSQL of below table

问题描述

我试图在跟踪中将跟踪表转换为结果表.我在表中有拥抱数据.

I was trying to convert the trace table to resulted table in postgress. I have hug data in the table.

我有一个名称为Trace的表

I have table with name : Trace

entity_id                         | ts                | key         | bool_v  |   dbl_v |   str_v   | long_v  |
---------------------------------------------------------------------------------------------------------------
1ea815c48c5ac30bca403a1010b09f1   | 1593934026155   | temperature   |         |         |           | 45      |
1ea815c48c5ac30bca403a1010b09f1   | 1593934026155   | operation     |         |         |   Normal  |         |
1ea815c48c5ac30bca403a1010b09f1   | 1593934026155   | period        |         |         |           | 6968    |
1ea815c48c5ac30bca403a1010b09f1   | 1593933202984   | temperature   |         |         |           | 44      |
1ea815c48c5ac30bca403a1010b09f1   | 1593933202984   | operation     |         |         |   Reverse |         |
1ea815c48c5ac30bca403a1010b09f1   | 1593933202984   | period        |         |         |           | 3535    |

跟踪表

将上表转换为PostgreSQL中的下表

convert the above table into following table in PostgreSQL

输出表:结果

entity_id                         | ts            | temperature | operation | period    |
----------------------------------------------------------------------------------------|
1ea815c48c5ac30bca403a1010b09f1   | 1593934026155 | 45          | Normal    | 6968      |
1ea815c48c5ac30bca403a1010b09f1   | 1593933202984 | 44          | Reverse   | 3535      |

结果表

推荐答案

您尝试过吗?

select entity_id, ts,
       max(long_v) filter (where key = 'temperature') as temperature,
       max(str_v) filter (where key = 'operation') as operation,
       max(long_v) filter (where key = 'period') as period
  from trace
 group by entity_id, ts;

这篇关于下表如何在PostgreSQL中将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！