SQL> SELECT * FROM employee;
EMPNO EMPNAME
----- --------------------
00001 andi
00002 badrun
00003 jaka
/* Pake fungsi SYS_CONNECT_BY_PATH
(Hanya u/ Oracle 9i ke atas) */
SQL> SELECT 'empno'||MAX(SYS_CONNECT_BY_PATH(empno,' '))||
2 -- tambahkan u/ setiap kolom berikut
3 CHR(10)||
4 'empname'||MAX(SYS_CONNECT_BY_PATH(empname,' '))
5 hasil_query
6 FROM (
7 SELECT rownum row_no, empno, empname
8 FROM employee
9 )
10 START WITH row_no = 1
11 CONNECT BY PRIOR row_no = row_no-1;
HASIL_QUERY
----------------------------------------------------------
empno 00001 00002 00003
empname andi badrun jaka
/* untuk versi Oracle lain bisa dibuatkan fungsi dulu */
SQL> CREATE OR REPLACE FUNCTION v_to_h
2 (col_name IN VARCHAR2, tab_name IN VARCHAR2)
3 RETURN VARCHAR2 IS
4 TYPE ref_cur IS REF CURSOR;
5 v_string VARCHAR2(4000);
6 v_path VARCHAR2(1);
7 v_val VARCHAR2(4000);
8 c1 ref_cur;
9 BEGIN
10 OPEN c1 for 'SELECT '||col_name||' FROM '||tab_name;
11 v_string := col_name;
12 v_path := ' ';
13 LOOP
14 FETCH c1 INTO v_val;
15 EXIT WHEN c1%NOTFOUND;
16 v_string := v_string||v_path||v_val;
17 END LOOP;
18 CLOSE c1;
19 RETURN v_string;
20 END;
21 /
Function created.
SQL> SELECT v_to_h('empno','employee') hasil_query
2 FROM DUAL
3 -- tambahkan u/ setiap kolom berikut
4 UNION ALL
5 SELECT v_to_h('empname','employee')
6 FROM DUAL;
HASIL_QUERY
--------------------------------------------------
empno 00001 00002 00003
empname andi badrun jaka
hth,
AH
Adnan Abdurrachman Bazargan wrote:
>
> salam,
> saya pengentau caranya untuk convert dari baris menjadi colom dengan
> perintah SQL mikik oracle itu seperti gmana?
>
> misal:
> empNo empName
> 00001 andi
> 00002 badrun
> 00003 jaka
>
> hasil querynya:
> empno 0001 0002 0003
> empname andi badrun jaka
>
> trima kasih sebelumnya.
> mohon bantuannya
>
> salam.
>
> ---------------------------------
> Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user
> panel and lay it on us.
>
> [Non-text portions of this message have been removed]
>
>
