/* 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
>
>
