postgres数据库中的concat列数据

这是简单的字符串连接:

select t,
       concat_ws(',', coalesce(max_type, ''), coalesce(mean_type, ''),
                 coalesce(min_type, ''), coalesce(std_type, '')
                ) as string_agg
       sid
from . . . ;

你确实需要小心类型。上面假设这些值实际上是字符串(尽管如果不是字符串,它们很容易被转换为字符串)。

您可以将查询构造为:

SELECT m.measurement_at AS t,
       concat_ws(',',
                 coalesce(MAX(CASE mc.type WHEN 'max' THEN VALUE END)::text, ''),
                 coalesce(MAX(CASE mc.type WHEN 'mean' THEN VALUE END)::text, ''),
                 coalesce(MAX(CASE mc.type WHEN 'min' THEN VALUE END)::text, ''), 
                 coalesce(MAX(CASE mc.type WHEN 'std' THEN VALUE END)::text, '')
                ) as string_agg,
        mc.sensor_id AS sId 
FROM flow.measure m INNER JOIN
     flow.measure_col mc 
     ON mc.id = m.measure_col_id INNER JOIN
     flow.sensors s 
     ON s.id = mc.sensor_id
WHERE s.station_id = 1
GROUP BY t, sId
ORDER BY t;