Edit some values in column sql

Question

I have a table:

id	param	value
1	p1	value1
1	p2	value2
1	p3	value3
1	p4	value4
2	p1	value10
2	p2	value20
2	p5	value50
3	p1	value100
3	p2	value200
3	p4	value400
4	p1	value11
4	p2	value12
4	p3	value12

I want to have the next result:

id	value
1	value1, val,value3
2	value10,val,value50
3	value100,val
4	value11, val, value12

I know that I can union into one line using listagg, but I don't know how to take only 3 values's letters from p2 and write it in one column with other values. My query:

 select listagg(p.value, ',') within group (order by p.id asc) from tab p 
where p.param like 'p1' or p.param like 'p3' or p.param like 'p4'
or p.value = (select SUBSTR(m.value, 0, 4) 
from tab m where m.param like 'p2' and m.id = p.id) 
group by p.id

I understand why my query is not working correct, but I don't understand how to do it

Answer 1

how to take only 3 values's letters from p2 and write it in one column with other values.

You can use conditional logic in the listagg():

select listagg(case when p.param = 'p2' then substr(p.value, 1, 3) else p.value end, ',') within group (order by p.id asc)
from tab p 
where p.param in ('p1', 'p2', 'p3', 'p4') 
group by p.id;