I would like to GROUP cols into 1 record using a distinctive name
id, code, date, name -------------- 1, code1, date1, name1 2, code2, date2, name1 3, code3, date3, name2 I would like the result to be
record1: "code1,date1,code2,date2", name1 record2: "code3,date3", name2 Do I use:
SELECT * FROM tablename GROUP BY code, date HAVING (name the same?) Please help. Thanks.
Here is one way of doing this
create table t(id int, code varchar(30),date1 varchar(10), name varchar(30)) insert into t values(1,'code1', 'date1', 'name1'); insert into t values(2,'code2','date2', 'name1'); insert into t values(3, 'code3', 'date3', 'name2'); select name ,replace(replace(cast(array_agg(col_txt) as varchar(1000)),'}',''),'{','') as concat_val from ( select id,code as col_txt,name from t union all select id,date1,name from t order by 3,1 )grp group by name db fiddle link
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=3306c2649615b2ca1ba3720d4a84defb
You can try using array_agg in your query. That will group the string in postgres.
with cte as ( select 1 as ID, 'code1' as code, 'date1' as Date, 'name1' as name union all select 2 as ID, 'code2' as code, 'date2' as Date, 'name1' as name union all select 3 as ID, 'code3' as code, 'date3' as Date, 'name2' as name) select name,array_agg (concat(concat('record',right(name,1), ' :'), concat(code,',', date))) from cte group by name Here is the fiddle: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=905ef890d803b21619da988045e779da
Output:
