PL SQL script to concatenate based on the column values

Question

I am stuck at one point while writing a PL SQL script.

My requirement is to find the matching asset numbers, concatenate them and insert into a new table based on logic. The logic here is see if there are matching asset numbers based on the key. If there are no matching assets but the key is the same, then concatenate all the assets.

This is how the source table looks like

KEY     PR_AST_NUM     SEC_AST_NUM

1-24N   1-2428         1-2428
1-24N   1-2428         1-3032
1-24N   1-2428         1-3032
1-28N   1-3032         1-3436
1-28N   1-3842         1-4042
1-28N   1-5054         1-7284

This is how the target table should look like

KEY     CONCAT

1-24N   1-2428||1-2428
1-24N   1-2428||1-3032
1-28N   1-3032||1-3436||1-3842||1-4042||1-5054||1-7284

In order to achieve this, I wrote the following code first. But it is not concatenating all the records that matches the key. Could you help me modify the code?

DECLARE

    K VARCHAR2(30);
    NK VARCHAR2(30);
    PST  VARCHAR2(30);
    NPST VARCHAR2(30);
    AST VARCHAR2(30);
    NAST VARCHAR2(30);

    M INTEGER(20) := 1;

    CURSOR PRDT IS 

    SELECT DISTINCT KEY,
           LEAD(KEY) OVER (ORDER BY KEY) AS NXTKEY,
           PR_AST_NUM,
           LEAD(PR_AST_NUM) OVER (ORDER BY KEY) AS NXTPRAST,
           SEC_AST_NUM,
           LEAD(SEC_AST_NUM) OVER (ORDER BY KEY) AS NXTSECAST
    FROM  DET_SRC
    ORDER BY KEY;

    BEGIN

    OPEN PRDT;

    LOOP

    FETCH PRDT INTO K, NK, PST, NPST, AST, NAST;

    EXIT WHEN PRDT%NOTFOUND;

    IF ((K = NK) AND (PST = NPST) AND (AST = NAST))
    THEN

    INSERT INTO DET_MOD
    VALUES (K, PST||'||'||AST);

    M := M + 1;

    ELSIF ((K = NK) AND (PST = NPST) AND (AST <> NAST))
    THEN

    M := 1;

    INSERT INTO DET_MOD
    VALUES (K, PST||'||'||AST);

    ELSIF ((K = NK) AND (PST <> NPST) AND (AST <> NAST))
    THEN

    INSERT INTO DET_MOD
    VALUES (K, PST||'||'||AST);

    M := 1;

    END IF;

    END LOOP;

    COMMIT;

    CLOSE PRDT;

    END;

This is the output of the code. As you can see, instead of concatenating to the next row, it is creating a new row and missing the last condition.

    KEY     CONCAT
    1-24N   1-2428||1-2428
    1-24N   1-2428||1-3032
    1-28N   1-3032||1-3436
    1-28N   1-3842||1-4042

Thanks !

Answer 1

Check This Live Demo

Use below query in your cursor or function or any other code to fetch result.

Code :

            select KEY, LISTAGG(AST_NUM, '||') WITHIN GROUP (ORDER BY KEY) 
            as CONCAT_ASSET_NUM
            from 
            (select   distinct KEY ,  Concat(PR_AST_NUM , Concat('||',SEC_AST_NUM))  
            as AST_NUM
            from DET
            where PR_AST_NUM=SEC_AST_NUM
            Order by KEY 

            )b 
            group by KEY


            union all

            select KEY, LISTAGG(AST_NUM, '||') WITHIN GROUP (ORDER BY KEY) 
            as CONCAT_ASSET_NUM
            from 
            (select   distinct KEY ,  Concat(PR_AST_NUM , Concat('||',SEC_AST_NUM))  
            as AST_NUM
            from DET
            where PR_AST_NUM!=SEC_AST_NUM
            Order by KEY 

            )a 
            group by KEY

Ouput :

Answer 2

Can you please try this:

SELECT val1, LISTAGG(val2, ', ') WITHIN GROUP (ORDER BY val2) AS value2
from (
    select KEY as val1, PR_AST_NUM as val2 from myTable 
    union all     
    select key, SEC_AST_NUM from myTable) t group by val1