sql - SQL Grouping of Columns Values

Question

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.

标签： sqlpostgresqlstring-aggregation

Answer 1

这是执行此操作的一种方法

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小提琴链接

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=3306c2649615b2ca1ba3720d4a84defb