How can I structure this Oracle SQL query

user_16760497 0
Viewed 61

I need to retrieve the name or names of "CARGO#" separated by “,” filtered by "PNTO_VNTA"

I need the output in this way:

I need the output in this way

I have done many tests but they bring me the result in this way:

I have done many tests but they bring me the result in this way

This is the code that I am using

 SELECT
    s.PNTO_VNTA,
    LISTAGG (g.CARGO), ', ') WITHIN GROUP (ORDER BY g.CARGO) OVER()  PRODUCTOS
FROM
    PNTOS_VNTA s,
    CARGOS g
WHERE
    s.crgo = g.CARGO
    AND s.PNTO_VNTA = 12345
    GROUP BY s.PNTO_VNTA,
    g.cargo
    ;
listagg oracle sql
1 Answers

You are grouping by both pnto_vnta and cargo, so you will get one row for each combination; the aggregate would then only have a single value. You seem to have tried to compensate for that by adding the over() clause to make the listagg() call analytic.

If you change both of those you should get what you want:

SELECT
    s.PNTO_VNTA,
    LISTAGG (g.CARGO, ', ') WITHIN GROUP (ORDER BY g.CARGO) PRODUCTOS
FROM
    PNTOS_VNTA s,
    CARGOS g
WHERE
    s.crgo = g.CARGO
    AND s.PNTO_VNTA = 12345
    GROUP BY s.PNTO_VNTA
    ;

Or with ANSI join syntax:

SELECT
    s.PNTO_VNTA,
    LISTAGG (g.CARGO, ', ') WITHIN GROUP (ORDER BY g.CARGO) PRODUCTOS
FROM PNTOS_VNTA s
JOIN CARGOS g ON s.crgo = g.CARGO
WHERE s.PNTO_VNTA = 12345
GROUP BY s.PNTO_VNTA
;

Although it doesn't look like you need the join; unless you have nulls or orphans you can probably just do:

SELECT
    s.PNTO_VNTA,
    LISTAGG (s.CRGO, ', ') WITHIN GROUP (ORDER BY s.CRGO) PRODUCTOS
FROM PNTOS_VNTA s
WHERE s.PNTO_VNTA = 12345
GROUP BY s.PNTO_VNTA
;

db<>fiddle

(I've left s.crgo in all of those; not sure if that's a typo in your question.)

user_2663040
Related