* Cross Tab dg SQL bertingkat, HermanT
* Set step on
SET ENGINEBEHAVIOR 70

Create cursor Pegawai ;
	(No_Id C(3), Nama C(10), Tanggal D, Jam C(5), Ketr C(10))
Insert into Pegawai ;
	Values ( '123', 'Asmien', {^2005-10-11}, '07.00', 'Masuk' )
Insert into Pegawai ;
	Values ( '123', 'Asmien', {^2005-10-11}, '16.00', 'Pulang' )
Insert into Pegawai ;
	Values ( '123', 'Asmien', {^2005-10-12}, '07.00', 'Masuk' )
Insert into Pegawai ;
	Values ( '123', 'Asmien', {^2005-10-12}, '16.00', 'Pulang' )
Insert into Pegawai ;
	Values ( '123', 'Asmien', {^2005-10-13}, '07.00', 'Masuk' )
Insert into Pegawai ;
	Values ( '123', 'Asmien', {^2005-10-13}, '17.00', 'Pulang' )

Insert into Pegawai ;
	Values ( '124', 'Fach', {^2005-10-11}, '07.00', 'Masuk' )
Insert into Pegawai ;
	Values ( '124', 'Fach', {^2005-10-11}, '16.00', 'Pulang' )
Insert into Pegawai ;
	Values ( '124', 'Fach', {^2005-10-12}, '07.00', 'Masuk' )
Insert into Pegawai ;
	Values ( '124', 'Fach', {^2005-10-12}, '13.00', 'Pulang' )
Index on No_Id+dtos(tanggal) tag No_Id

Select distinct No_Id, Nama ;
	From Pegawai ;
	Group by No_Id ;
	Order by No_Id ;
	Into cursor curAbsen ReadWrite

Select distinct Tanggal ;
	From Pegawai ;
	Group by Tanggal ;
	Order by Tanggal ;
	Into cursor curTanggal

Go top
Scan while !eof()
	** Field untuk Tanggal - Jam masuk
	cField = 'M_' + dtos( Tanggal ) + ' C(10)'
	Alter table curAbsen add column &cField

	** Field untuk Tanggal - Jam pulang
	cField = 'P_' + dtos( Tanggal ) + ' C(10)'
	Alter table curAbsen add column &cField
EndScan

Select curAbsen
Go top
Set order to 
Set relation to No_Id into Pegawai
Set skip to Pegawai
Scan while !eof()
	cField = left( Pegawai->Ketr, 1 ) + '_' + dtos( Pegawai->Tanggal )
	Replace &cField with Pegawai->Jam
EndScan
Set skip to
Set relation to

Browse nomodify
Close all