Skip to Content

Concatenate data within each dimension

Consider a table COMP with two fields A and B as below,

A B
M John
M Mike
M Peter
F Ayrene
F Mitchelle
F Veronic

To concatenate the data,grouped by the Org_Details.ie: To get a table as below

M John,Mike,Peter
F Ayrene,Mitchelle,Veronic

1.Create a function

CREATE OR REPLACE

  FUNCTION CONVERT_MUL_SING_COLUMN(v_sql_b_name IN COMP.a%TYPE)

    RETURN VARCHAR2 IS

    b VARCHAR2(100) := NULL;

    BEGIN

      FOR cur_rec IN

      (SELECT B FROM COMP WHERE a = v_sql_b_name)

      LOOP

        B := B || ‘,’ || cur_rec.B;

      END LOOP;

      RETURN LTRIM(B, ‘,’);

      END;   

2. Execute the below query

select a,CONVERT_MUL_SING_COLUMN(‘M’) from comp group by a,CONVERT_MUL_SING_COLUMN(‘M’)

Environment: Oracle Server

Be the first to leave a comment
You must be Logged on to comment or reply to a post.