Listagg to get certain values in string and leave other values seperate

Question

I'm trying to get some values from a table into a string and want to leave some out. A different column would determine which values I want in the string and which ones I want to leave out of the string.

The table (table2) I'm using for the string:

t_id	attribute	code
1123	a	4rt
1123	a	8nf
1123	b	9sa
1123	c	8sf
1123	d	6df
1123	b	3er
1231	a	8nf
1231	b	3fg
1231	d	6ty
1231	c	4rg
1231	b	0rg

The query I used to get the full string for every t_id in the table:

select distinct t0.*, t3.pool,

listagg(distinct t1.code ,',) within group(order by t1.t_id)
over (partition by t1.t_id) as "CODES"

from table1 t0

left join table2 t1
on t0.id = t1.t_id

left join table3 t2
on t0.id = t2.t_id

left join table4 t3
on t0_id = t3.t_id

where t0.active = '1'
and t2.someother_id = '123456789'

What I need is something where I can get a string for all codes from table2 where attribute = a and b and I would need attribute c and d seperate like:

t_id	attribute	code
1123	a,b	4rt, 8nf, 9sa, 3er
1123	c	8sf
1123	d	6df
1231	a,b	8nf, 3fg, 0rg
1231	c	4rg
1231	d	6ty

Answer 1

Don't use DISTINCT and analytic functions, use aggregation when you JOIN to table2 and a CASE expression in the GROUP BY clause so that a and b values are in the same group:

SELECT t1.*,
       t4.pool,
       t2.codes
FROM   table1 t1
       LEFT OUTER JOIN (
         SELECT t_id,
                LISTAGG(DISTINCT code, ',') WITHIN GROUP (ORDER BY t_id)
                  AS codes
         FROM   table2
         GROUP BY t_id,
                  CASE
                  WHEN attribute IN ('a', 'b')
                  THEN 'a, b'
                  ELSE attribute
                  END
       ) t2
       ON t1.id = t2.t_id
       INNER JOIN table3 t3
       ON t1.id = t3.t_id
       LEFT OUTER JOIN table4 t3
       ON t1.id = t4.t_id
WHERE  t1.active = '1'
AND    t3.someother_id = '123456789'

Note: Do not have tables called table1, table2, etc. and give them aliases t0, t1, etc. as it is unnecessarily confusing. Use aliases that relate to your table names such as t1, t2, etc.

Note: The WHERE clause changes table3 from being LEFT OUTER JOINed to INNER JOINed as the t3.someother_id = '123456789' filter will never be true when the table is OUTER joined.

---

If you want to include the attributes then depending on what you want to display:

If you want to always display a, b even if only a or b is present then:

SELECT t1.*,
       t4.pool,
       t2.attributes,
       t2.codes
FROM   table1 t1
       LEFT OUTER JOIN (
         SELECT t_id,
                CASE
                WHEN attribute IN ('a', 'b')
                THEN 'a, b'
                ELSE attribute
                END AS attributes,
                LISTAGG(DISTINCT code, ',') WITHIN GROUP (ORDER BY t_id)
                  AS codes
         FROM   table2
         GROUP BY t_id,
                  CASE
                  WHEN attribute IN ('a', 'b')
                  THEN 'a, b'
                  ELSE attribute
                  END
       ) t2
       ON t1.id = t2.t_id
       INNER JOIN table3 t3
       ON t1.id = t3.t_id
       LEFT OUTER JOIN table4 t3
       ON t1.id = t4.t_id
WHERE  t1.active = '1'
AND    t3.someother_id = '123456789'

or, if you want to display a, b or a, b depending on what is in the table then:

SELECT t1.*,
       t4.pool,
       t2.attributes,
       t2.codes
FROM   table1 t1
       LEFT OUTER JOIN (
         SELECT t_id,
                LISTAGG(DISTINCT attribute, ',') WITHIN GROUP (ORDER BY attribute)
                  AS attributes,
                LISTAGG(DISTINCT code, ',') WITHIN GROUP (ORDER BY t_id)
                  AS codes
         FROM   table2
         GROUP BY t_id,
                  CASE
                  WHEN attribute IN ('a', 'b')
                  THEN 'a, b'
                  ELSE attribute
                  END
       ) t2
       ON t1.id = t2.t_id
       INNER JOIN table3 t3
       ON t1.id = t3.t_id
       LEFT OUTER JOIN table4 t3
       ON t1.id = t4.t_id
WHERE  t1.active = '1'
AND    t3.someother_id = '123456789'

or, if you want a list that has attributes that correspond to the code values (so may contain multiple a or b values):

SELECT t1.*,
       t4.pool,
       t2.attributes,
       t2.codes
FROM   table1 t1
       LEFT OUTER JOIN (
         SELECT t_id,
                LISTAGG(attribute, ',') WITHIN GROUP (ORDER BY t_id)
                  AS attributes,
                LISTAGG(code, ',') WITHIN GROUP (ORDER BY t_id)
                  AS codes
         FROM   (
           SELECT DISTINCT
                  t_id,
                  attribute,
                  code
           FROM   table2
         )
         GROUP BY t_id,
                  CASE
                  WHEN attribute IN ('a', 'b')
                  THEN 'a, b'
                  ELSE attribute
                  END
       ) t2
       ON t1.id = t2.t_id
       INNER JOIN table3 t3
       ON t1.id = t3.t_id
       LEFT OUTER JOIN table4 t3
       ON t1.id = t4.t_id
WHERE  t1.active = '1'
AND    t3.someother_id = '123456789'