在PostgreSQL中基于日期/时间的行到列 [英] Rows to columns based on date/time in Postgresql

问题描述

这是表的一部分:

我需要将其转换为:

I need to transform it to this:

我使用了聚合，但是它填充了很多空单元格.当然，表中还有更多带有不同时间戳的记录，例如:09/02/2020 21:30:00等. 我看到类似的问题，但我的想法是莫名其妙地在sqlfiddle中搞砸了.

I used aggregation but it fills with a lot of null cells. Of course there are more records with different timestamp in the table like : 09/02/2020 21:30:00 and so on.. I see similar question but I thing is somehow messed up in the sqlfiddle.

推荐答案

您可以使用条件聚合来实现.

You can achieve this using conditional aggregation.

select recordtime, 
       max(formattedvalue) filter (where fullname = 'FirstName') as "First Name",
       max(formattedvalue) filter (where fullname = 'SecondName') as "Second Name",
       max(formattedvalue) filter (where fullname = 'ThirdName') as "Third Name", 
       .... repeat for all possible full names ...
       max(qualitydesc) as "QualityDesc", 
       max(statedesc) as "StateDesc"
from the_table
group by recordtime
order by recordtime;

这篇关于在PostgreSQL中基于日期/时间的行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！