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.

Which database are you using? Please specify with a tag (i.e. mySQL, PostgreSQL, etc) — Matt, Mar 16 '20 at 03:02

score 1 · Answer 1 · answered Mar 16 '20 at 03:51

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

score 0 · Answer 2 · answered Mar 16 '20 at 03:50

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:

SQL Grouping of Columns Values

2 Answers2