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 |
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'