Makasih Mas Awal, aku sudah nyoba dan berhasil
Tks
Arief
Awaluddin Hamid wrote:
>
> /* Oracle 9i or later */
> Query:
> SELECT karyawan,
> LTRIM(MAX(SYS_CONNECT_BY_PATH(training,',')),',') training
> FROM
> (SELECT karyawan, training,
> ROW_NUMBER() OVER (PARTITION BY karyawan ORDER BY training) row_no
> FROM the_table)
> START WITH row_no = 1
> CONNECT BY PRIOR row_no = row_no-1 AND PRIOR karyawan = karyawan
> GROUP BY karyawan
> ORDER BY 1
>
> /* Oracle 8i or earlier */
> Fungsi:
> CREATE OR REPLACE FUNCTION concat_func(v_table VARCHAR2,
> v_column1 VARCHAR2, v_column2 VARCHAR2, v_where VARCHAR2)
> RETURN VARCHAR2 IS
> TYPE ref_cur IS REF CURSOR;
> v_value VARCHAR2(256);
> v_concat VARCHAR2(4000) := '';
> c1 ref_cur;
> BEGIN
> OPEN c1 FOR 'SELECT '||v_column1||
> ' FROM '||v_table||
> ' WHERE '||v_column2||' = :1'||
> ' ORDER BY 1'
> USING v_where;
> LOOP
> FETCH c1 INTO v_value;
> EXIT WHEN c1%NOTFOUND;
> v_concat := v_concat||v_value||',';
> END LOOP;
> CLOSE c1;
> RETURN RTRIM(v_concat,',');
> EXCEPTION
> WHEN OTHERS THEN
> RAISE_APPLICATION_ERROR(-20001, 'Function error');
> END;
>
> Query:
> SELECT karyawan,
> MAX(concat_func('the_table','training','karyawan',karyawan)) training
> FROM the_table
> GROUP BY karyawan
>
> CMIIW,
> AH
>
> Mohamad Arief Iskandar wrote:
> >
> > Dear All
> >
> > Aku mau tanya bagaimana cara concat dari record yang berbeda, misal ada
> > data seperti ini
> >
> > Karyawan Training
> > --------------------------------
> > A X1
> > A X2
> > A X3
> > B X1
> > B X3
> > C X1
> > D X1
> > D X2
> >
> > ingin saya query sehingga hasilnya menjadi
> >
> > Karyawan Training
> > -------------------------------------
> > A X1,X2,X3
> > B X1,X3
> > C X1
> > D X1,X2
> >
> > Tks
> > Arief
> >
> >
>
>
