LISTAGG function to concatenate strings and search for explicit string values in Redshift does not w-CodePudding

I have a table project. I would like to concatenate all projects per id in one row and filter for only for ids which do have the project 'a' but not the project 'c'.

Table: project

id	project
1	a
1	b
1	c
2	a
2	b
2	d

Result

id	project
2	a,b,d

I tried several approaches, among others this one:

SELECT
  id, 
  LISTAGG(project, ',') AS project
  FROM project
  WHERE project NOT IN ('c') AND project in ('a')
  GROUP BY id

SELECT
  id, 
  LISTAGG(project, ',') AS project
  FROM project
  WHERE project != 'c' AND project = 'a'
  GROUP BY id

Or this one:

SELECT id, 
       LISTAGG(project, ',') 
       FROM (
            SELECT person_id,
                   project
            FROM project
            WHERE project IN 
                           (
                            SELECT project
                            FROM project
                            WHERE project != 'c' AND project = 'a'
                            )
             )
       GROUP BY id

All of the above approaches do not filter properly and do not exclude ids with project 'c'.

CodePudding user response：

Since you're grouping the result anyway (for the listagg), a neat trick is to count the occurrences of 'a' (should be more than 0) and 'c' (should be exactly 0):

SELECT   id,  LISTAGG(project, ',') AS project
FROM     project
GROUP BY id
HAVING   COUNT(CASE project WHEN 'a' THEN 1 END) > 0 AND
         COUNT(CASE project WHEN 'c' THEN 1 END) = 0