Tag: sql

Posted on

Dynamic Pivot in SQL

Berikut adalah alternatif query untuk menampilkan pivot table dari 2 (dua) tabel. Contoh kedua tabel adalah sebagai berikut:

Table_1

kodenama
T1Tunjangan Allowance
T2Tunjangan THR
T3Tunjangan Jabatan

Table_2

kodenilai
T11000
T22000
T3500

Hasil dari pivot yang diinginkan adalah sebagai berikut:

Tunjangan Allowance Tunjangan THR Tunjangan Jabatan
10002000500

Untuk mendapatkan hasil tersebut gunakan query berikut:

DECLARE @CQUERY AS VARCHAR(MAX)
DECLARE @CKODE VARCHAR(50),
     @CNAMA VARCHAR(50)
DECLARE C_Cursor INSENSITIVE CURSOR
FOR
     SELECT kode, nama
     FROM Table_1
SET @CQUERY = 'SELECT '
OPEN C_Cursor
FETCH NEXT FROM C_Cursor INTO @CKODE, @CNAMA
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @CQUERY = @CQUERY + 'SUM(CASE WHEN kode = ''' + @CKODE + ''' THEN nilai ELSE 0 END) AS [' + @CNAMA + ']' 
 FETCH NEXT FROM C_Cursor INTO @CKODE, @CNAMA 
 IF @@FETCH_STATUS = 0 SET @CQUERY = @CQUERY + ', ' + CHAR(13)
END
CLOSE C_Cursor
DEALLOCATE C_Cursor
SET @CQUERY = @CQUERY + CHAR(13) + 'FROM Table_2 '
EXECUTE(@CQUERY)

Semoga bermanfaat…

Posted on

Get Last Item Per Group

I have a table A with a trigger for insert to update or insert if not exists, another table B with the last information for a defined combination of a group of foreign key. But, sometimes things aren’t going well and somehow the last information kept in B is not the actual last information inserted in A. So, to fix the data error, I need to get the last record from A for each group of foreign key.

Here’s how (credit to Stackoverflow.com user Bill Karwin in https://stackoverflow.com/questions/1505549/how-to-make-a-sql-query-for-last-transaction-of-every-account):


SELECT A.CAPPS_CODE, A.CCUSTOMER_CODE, A.CSERVER_TYPE, A.CSERVER_UID
FROM LAT_SERVER_REG A
LEFT OUTER JOIN LAT_SERVER_REG B
ON B.CAPPS_CODE = A.CAPPS_CODE
AND B.CCUSTOMER_CODE = A.CCUSTOMER_CODE
AND B.CSERVER_TYPE = A.CSERVER_TYPE
AND B.CREGISTRATION_ID > A.CREGISTRATION_ID
WHERE B.CAPPS_CODE IS NULL

The key is in the last line WHERE B.CAPPS_CODE IS NULL. CREGISTRATION_ID is a record id which hold the information of time in a format of yyyymmdd-hhMMss, so by comparing the field with the other “imaginary” set of records from the same table, we get that the last CREGISTRATION_ID does not have any counterpart record, hence B.CAPPS_CODE IS NULL. Brilliant isn’t it?