Concat results from one column

Question

How to concat results from column in Oracle SQL. same function as GROUP_CONCAT().

I have data like this

I want to get the result like this.

I already used listagg()

SELECT LISTAGG(S.MEASURE_WEIGHT, '/') WITHIN GROUP (ORDER BY S.Plan_id)

result are:

Without zeroes :( can someone help me?

https://stackoverflow.com/questions/16771086/is-there-any-function-in-oracle-similar-to-group-concat-in-mysql Check this out. — Abhinaba Chakraborty, Jun 30 '20 at 19:26

GMB · Answer 1 · 2020-06-30T20:35:55.337

0

It seems like you want conditional aggregation:

select 
    measure_id,
    max(case when plan_id = 1050 then measure_weight else 0 end)
        || '/'
        || max(case when plan_id = 1055 then measure_weight else 0 end) weight
from mytable
group by measure_id

edited Jun 30 '20 at 20:35

answered Jun 30 '20 at 19:56

GMB

216,147
25
84
135

I already used that and the result are: 53 | 4.9/4.5 ------ 82 | 5 ------ 81 | 5 I need it to be like this 53 | 4.9/4.5 ------ 82 | 5/0 ------ 81 | 0/5 – Vanessa Laine Gorre Jun 30 '20 at 20:27
@VanessaLaineGorre: I gave a second look at your question and came up with another solution. See my updated answer. – GMB Jun 30 '20 at 20:36

Nowhere Man · Answer 2 · 2020-06-30T21:20:07.490

Possibly this query using two subqueries may work:

SELECT
    measure_id,
    TO_CHAR(NVL((
        SELECT weight FROM table t1 
        WHERE t1.measure_id = t.measure_id AND t1.plan_id = t.min_id AND t.min_id = 1050), 0)) 
    || '/' ||
    TO_CHAR(NVL((
        SELECT weight FROM table t2 
        WHERE t2.measure_id = t.measure_id AND t2.plan_id = t.max_id AND t.max_id = 1055), 0))
FROM (
    SELECT measure_id, min(plan_id) min_id, max(plan_id) max_id
    FROM table
    GROUP BY measure_id
) t;

Concat results from one column

2 Answers2