Mudah2an maksudnya begini:
SQL> CREATE TABLE awal (
2 rem1 NUMBER(3),
3 rem2 NUMBER(3),
4 rem3 NUMBER(3),
5 ujian VARCHAR2(3),
6 nim VARCHAR2(3),
7 matkul VARCHAR2(10));
Table created.
SQL> INSERT INTO awal VALUES (60,80,90,'UH1','01','ipa');
1 row created.
SQL> INSERT INTO awal VALUES (70,90,87,'UH2','01','ipa');
1 row created.
SQL> INSERT INTO awal VALUES (87,65,90,'UH3','01','ipa');
1 row created.
SQL> INSERT INTO awal VALUES (70,87,68,'UH4','01','ipa');
1 row created.
SQL> INSERT INTO awal VALUES (80,54,87,'UH1','02','ipa');
1 row created.
SQL> INSERT INTO awal VALUES (90,77,90,'UH2','02','ipa');
1 row created.
SQL> INSERT INTO awal VALUES (80,87,67,'UH3','02','ipa');
1 row created.
SQL> INSERT INTO awal VALUES (90,79,66,'UH4','02','ipa');
1 row created.
SQL> COLUMN i FORMAT A4
SQL> SET HEADING OFF
SQL> SELECT 'NIM' i,NULL i,'UH1' i,NULL i,NULL i,'UH2' i,NULL i,
2 NULL i,'UH3' i,NULL i,NULL i,'UH4' i,NULL i FROM DUAL
3 UNION ALL
4 SELECT '','rem1','rem2','rem3','rem1','rem2','rem3',
5 'rem1','rem2','rem3','rem1','rem2','rem3' FROM DUAL
6 UNION ALL
7 SELECT nim, TO_CHAR(SUM(DECODE(ujian,'UH1',rem1,NULL))),
8 TO_CHAR(SUM(DECODE(ujian,'UH1',rem2,NULL))),
9 TO_CHAR(SUM(DECODE(ujian,'UH1',rem3,NULL))),
10 TO_CHAR(SUM(DECODE(ujian,'UH2',rem1,NULL))),
11 TO_CHAR(SUM(DECODE(ujian,'UH2',rem2,NULL))),
12 TO_CHAR(SUM(DECODE(ujian,'UH2',rem3,NULL))),
13 TO_CHAR(SUM(DECODE(ujian,'UH3',rem1,NULL))),
14 TO_CHAR(SUM(DECODE(ujian,'UH3',rem2,NULL))),
15 TO_CHAR(SUM(DECODE(ujian,'UH3',rem3,NULL))),
16 TO_CHAR(SUM(DECODE(ujian,'UH4',rem1,NULL))),
17 TO_CHAR(SUM(DECODE(ujian,'UH4',rem2,NULL))),
18 TO_CHAR(SUM(DECODE(ujian,'UH4',rem3,NULL)))
19 FROM awal
20 GROUP BY nim;
NIM UH1 UH2 UH3 UH4
rem1 rem2 rem3 rem1 rem2 rem3 rem1 rem2 rem3 rem1 rem2 rem3
01 60 80 90 70 90 87 87 65 90 70 87 68
02 80 54 87 90 77 90 80 87 67 90 79 66
hth,
AH
Hume Humaedi wrote:
>
> halo semuanya ....
>
> saya newbie nih !! , kalo kita mo buat pivot table yang bentuknya
> seperti di bawah ini gimana ya :
>
> tabel awal
> +--------+--------+--------+---------+--------+----------+
> | rem1 | rem2 | rem3 | ujian | NIM |matkul |
>
> +--------+--------+--------+---------+--------+----------+
> | 60 | 80 | 90 | UH1 | 01 | ipa |
> | 70 | 90 | 87 | UH2 | 01 | ipa |
> | 87 | 65 | 90 | UH3 | 01 | ipa |
> | 70 | 87 | 68 | UH4 | 01 | ipa |
> | 80 | 54 | 87 | UH1 | 02 | ipa |
> | 90 | 77 | 90 | UH2 | 02 | ipa |
> | 80 | 87 | 67 | UH3 | 02 | ipa |
> | 90 | 79 | 66 | UH4 | 02 | ipa |
> +--------+--------+--------+---------+---------+---------+
>
> trus pengen di buat seperti
> +-------------------------------------
> ----+---------------------------+---------------------------+--------------------------+
> |NIM | UH1 | UH2 |
> UH3 | UH4
> + +----------------------------------------------------------+
> | | rem1 rem2 rem3 rem1 rem2 rem3 rem1 rem2
> rem3 rem1 rem2 rem3
> +---------+----------------------------------------------------------+
> 01 | 60 80 90 70 90 87 87
> 65 90 70 87 68
> 02 | 80 54 87 90 77 90 80
> 87 67 90 79 66
> +---------+----------------------------------------------------------+
>
> penjelasan tabel di atas :
> siswa melaksanakan ujian harian (UH) sebanyak 4 kali , dimana setiap
> ujian hariannya siswa melakuakn remedial max sebanyak 3 kali (rem) dan
> masing2 mendapatkan nilai, misalkan untuk mata pelajaran ipa , kira 2
> bisa gak di buat laporannya kayak tabel ke dua di atas , klo bisa
> Querynya gimana ya ??
> mohon pencerahannya , lg mendesak mo laporan KKP.
> terimakasih.
> --
> nuhun pisan
>
> -humaedi-
>
>
