how to concat the rows in postgres sql using case statement from 4tables

Question

emp_id   project_id  TASK_11     TASK_12     TASK_13    TASK_14
42527     4           no         no          no         yes
42527     4           no         yes         no         no
42527     4           yes        no          no         no
42527     4            no        no         yes         no

in the above table i want to display all emp_id's are need to come once. means

emp_id   project_id  TASK_11     TASK_12     TASK_13    TASK_14
42527     4           yes        yes         yes            yes

i want to display like this am using below command please let me know where need to modify

select distinct(b.emp_id),c.project_id,
(CASE when task_id='11' then 'yes'
    else 'no'
END) as "TASK_11",
(CASE 
    when task_id='12' then 'yes'
else 'no'
END) as "TASK_12",
(CASE 
    when task_id='13' then 'yes'
    else 'no'
END) as "TASK_13",
(CASE 
    when task_id='14' then 'yes'
    else 'no'
end) as "TASK_14"
FROM "Projects".user_projects a,
    "Projects".user_clients b,
    "Projects".project_tasks c,
    "Projects".user_tasks d,
    "Projects".projects e
WHERE  c.id = d.id AND a.id = b.id AND e.client_id=b.client_id 
    AND a.project_id=c.project_id and b.client_id=e.client_id
    and(b.emp_id = ANY (ARRAY[45262, 42572, 42527, 40904, 808, 909, 109, 
        147, 70707, 41111, 41041, 80808, 4235, 22, 428882, 4001100,
        40904, 42527, 42581, 42581, 12, 12, 42501, 42501, 202, 47100,
        42501, 4001100, 6389, 20240, 11662, 23556, 30480])) 
    AND e.project_name = 'wms' and c.project_id=4

Answer 1

Consider a straightforward conditional aggregate query directly running MAX on your logical CASE statements.

Additionally, consider re-factoring your SQL to use explicit joins (a 25+ year standard introduced in ANSI-92) and not the older, hard to read and maintain implicit joins as you current have. There is no change in performance but arguably easier to read and maintain with explicit JOIN.

SELECT b.emp_id, c.project_id,
       MAX(CASE WHEN task_id='11' THEN 'yes' ELSE 'no' END) AS "TASK_11",
       MAX(CASE WHEN task_id='12' THEN 'yes' ELSE 'no' END) AS "TASK_12",
       MAX(CASE WHEN task_id='13' THEN 'yes' ELSE 'no' END) AS "TASK_13",
       MAX(CASE WHEN task_id='14' THEN 'yes' ELSE 'no' END) AS "TASK_14"
FROM "Projects".user_projects a
INNER JOIN "Projects".user_clients b ON b.id = a.id = b.id
INNER JOIN "Projects".project_tasks c ON c.project_id = a.project_id
INNER JOIN "Projects".user_tasks d ON c.id = d.id
INNER JOIN "Projects".projects e ON e.client_id = b.client_id 
WHERE b.emp_id = ANY (ARRAY[45262, 42572, 42527, 40904, 808, 909, 109, 
                            147, 70707, 41111, 41041, 80808, 4235, 22, 428882, 4001100,
                            40904, 42527, 42581, 42581, 12, 12, 42501, 42501, 202, 47100,
                            42501, 4001100, 6389, 20240, 11662, 23556, 30480])
  AND e.project_name = 'wms' and c.project_id = 4
GROUP BY b.emp_id, c.project_id