Pencarian secara Row pada SQL Server
Soeleman 23 Maret 2017
Mencari berdasarkan column adalah hal biasa yang kita lakukan bila bekerja dengan database. Kalau mencari berdasarkan row, apakah memungkinkan dilakukan pada SQL? Untuk mencari tahu, kita coba siapkan data untuk menjawab pertanyaan ini.
Table
Untuk membuatnya sederhana kita akan menggunakan 2 table.
Table Patterns
Table ini berisi data yang akan kita gunakan untuk mencari. Dalam contoh ini kita mencari ABC, CB dan seterusnya. Pada column Pattern kita masukkan pattern yang akan kita coba cari di-table stream. Kenapa kita perlu table untuk ini? Kita akan coba juga mencari multi-pattern dalam pencariannya.
Table Stream
Table ini berisi column Code yang akan kita cari dengan row-nya. Seperti yang terlihat, data itu akan kita coba match-kan dengan pattern yang ada diatas.
Tujuan Pencarian
Kita ingin mencari di-column Code, tapi dengan melihat dari row-nya. Contohnya pattern ABC (warna merah), maka harus-nya kita mendapatkan 2 dari hasil pencarian kita. Dengan data yang ada, akan di dapat column Id 2-3-4 dan 6-7-8. Begitu juga dengan pattern CB (warna orange). Kita akan dapatkan column Id 4-5.
TSQL
Perhatikan query dibawah. T-SQL dibawah memiliki beberapa bagian yang menarik untuk menjawab pertanyaan diatas.
Query diatas terdiri dari 5 bagian. Kita akan melihat bagian perbagian secara detail.
Data dan Pattern
Baris 1 - 15, bagian ini dimaksudkan untuk mempersiapkan data dengan mengabungkan pattern dengan data-row (yang ada di table stream) yang dijadikan serial string. Disini kita manfaatkan fungsi FOR XML untuk membuat serial string dari column Code
Index Data Stream
Baris 16 - 22, disini juga kita persiapkan data yang akan digunakan pada bagian 5 (Penjumlahan). Disini kita menggunakan fungsi ROW_NUMBER agar mempermudahkan penyatuan data.
Pencarian
Baris 23 - 58, inti dari query kita ini ada disini. Disini kita mencari row (yang kita sudah jadikan serial string) dengan pattern dan dari sana kita juga mendapatkan position-number dari hasil pencarian itu. Pencarian serial string ini kita lakukan dengan fungsi CHARINDEX.
Map Index
Baris 68 - 84, hasil dari bagian 3 (Pencarian) akan kita buat menjadi row kembali. Dengan begitu kita akan bisa men-map Index Character dengan Id yang ada di-table stream.
Penjumlahan
Baris 85 - 93, hasil dari bagian 4 (Map Index) akan kita gabungkan dengan data pada bagian 2 (Index Data Stream). Kita bisa menghubungkan ke 2 resultset itu dengan column No (yang didapat dari fungsi ROW_NUMBER) dan position-number (yang didapat dari fungsi CHARINDEX dan map-index). Setelah itu kita gunakan fungsi SUM untuk mendapatkan total dari column Value dari table stream.
Penutup
T-SQL diatas memang bisa menjadi solusi. Tapi perlu di ingat cara ini kurang optimize apa bila diterapkan pada aplikasi sebenarnya. Ada beberapa hal yang bisa dimaksimalkan tekniknya, tapi sebagai proof-of-concept bahwa kita bisa melakukan apa yang menjadi tantangan pencarian row. Jawabannya bisa dengan cara diatas.
Referensi
Perhatian! Code yang ditampilkan dalam tulisan ini merupakan ilustrasi dari yang ingin dipaparkan dan bukan production ready code. Sudah banyak kejadian karena asal meng-copy-and-paste tanpa mengerti code yang diambil itu ke dalam production. Selain itu perlu ada tambahan code dan test sebelum siap untuk digunakan secara utuh.