Concat columns data in postgres DB

Question

I have a query which display the data like this:-

t                    max_type   mean_type   min_type    std_type    sid
 2010-04-17 00:00:00 5.300       4.3372,     3.4000,     null        1      
 2010-04-17 00:00:00 5.3135      null        3.0365      0.3795      2

i want to display the data something like this :-

 t                      string_agg                 sid
'2010-04-17 00:00:00',  5.3000,4.3372,3.4000,        1
'2010-04-17 00:00:00',  5.3135,,3.0365,0.3795        2

Query used by me:-

SELECT m.measurement_at AS t,MAX(CASE mc.type WHEN 'max' THEN VALUE END) 
AS max_type,MAX(CASE mc.type WHEN 'mean' THEN VALUE END) AS mean_type,
MAX(CASE mc.type WHEN 'min' THEN VALUE END) AS min_type,MAX(CASE mc.type WHEN 'std' THEN VALUE END)
AS std_type,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

Gordon Linoff · Accepted Answer · 2018-05-30 22:13:27Z

This is simple string concatenation:

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

You do need to be careful about types. The above assumes the values are actually strings (although they are easily cast to strings if they are not).

You can construct your query as:

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;

Collectives™ on Stack Overflow

Concat columns data in postgres DB

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related