PostgreSQL: How to aggregate text so it show in the same row? (grouping by id)-CodePudding

From a group of student names, I'd like to identify which student performed which step per id.

This is the output I get:

id step1    step2    step3
1  Sam
1           John
2  Ana      
2           Charlie
2                     Bob
3           Alex

which I obtain with the code:

select id,
     case when step = 'A' then student_name end as "step1",
     case when step = 'B' then student_name end as "step2",
     case when step = 'C' then student_name end as "step3"
from my_table
group by id

This is the output I'd like to get:

id step1    step2    step3
1  Sam      John
2  Ana      Charlie  Bob
3           Alex

If the names were numbers, I'd do:

select id,
     sum(case when step = 'A' then student_name end) as "step1",
     sum(case when step = 'B' then student_name end) as "step2",
     sum(case when step = 'C' then student_name end) as "step3"
from my_table
group by id

However, this cannot be performed with text. How can I modify the query to achieve the output above?

CodePudding user response：

your data

CREATE TABLE mytable(
   id    INTEGER  
  ,step1 VARCHAR(30) 
  ,step2 VARCHAR(30)
  ,step3 VARCHAR(30)
);
INSERT INTO mytable
(id,step1,step2,step3) VALUES 
(1,'Sam',NULL,NULL),
(1,NULL,'John',NULL),
(2,'Ana',NULL,NULL),
(2,NULL,'Charlie',NULL),
(2,NULL,NULL,'Bob'),
(3,NULL,'Alex',NULL);

just use max function

SELECT id,
       Max(step1) step1,
       Max(step2) step2,
       Max(step3) step3
FROM   mytable
GROUP  BY id
ORDER  BY id ASC

dbfiddle