SQL Server ~ Membuat Data Hierarki Dengan Rekursi

Hari ini mendapatkan soal dari prospect-client tentang SQL, intinya ini adalah kasus umum dimana sebuah tabel memiliki informasi hierarki ke dirinya sendiri (Tree).

Tabel dengan Hierarki

Tabel dengan Hierarki

Tabel diatas merupakan tabel Worker dimana disana ditentukan seorang Worker bisa membawahi worker lain, dan worker bawahannya juga bisa memiliki bawahan-bawahan sampai sejumlah n.

Ini bisa berlaku ke struktur menu, struktur lineage keluarga, strktur pangkat dan struktur apapun yang berbentuk n-tree.

Isi datanya

Isi datanya

Contoh data diatas Reed memiliki ManagerId null artinya dia ada diatas puncak Tree, Ted memiliki ManagerId 1, artinya dia anak buah Reed. Dan begitu seterusnya.

Jika kita disuruh untuk mendapatkan hierarki satu atau dua level dari seseorang sih mudah. Dapatkan saja ManagerId mereka dengan sebuah JOIN ke tabel itu sendiri.

SELECT w1.WorkerId, w1.FullName, w1.ManagerId, w2.FullName AS ManagerName
FROM Worker AS w1 INNER JOIN Worker AS w2 ON w2.WorkerId = w1.ManagerId

Hasilnya…

Satu Level

Satu Level

Bagaimana jika kamu disuruh menampilkan data manager untuk semua orang secara lengkap? 😀

Disinilah recursive query diperlukan

WITH WorkerList(WorkerID, FullName, ManagerName) AS
(
	SELECT
		Worker.WorkerId,
		Worker.FullName,
		CAST('' AS  nchar(20)) as ManagerName
	FROM Worker
		WHERE (Worker.ManagerId IS NULL)
	UNION ALL
	SELECT
		Worker.WorkerId,
		Worker.FullName,
		WorkerList.FullName AS ManagerName
	FROM Worker
		INNER JOIN WorkerList ON
		WorkerList.WorkerId = Worker.ManagerId
)
SELECT * FROM WorkerList

As you can see, kita membuat sebuah with Expression bernama WorkerList, dan jika kamu notice di UNION ke dua.. ada INNER JOIN ke WorkerList. (with yang isinya mengacu pada with itu sendiri.. sebuah rekursi!)

WITH itu merupakan Common Table Expression, bayangkan itu sebagai temporary-table (variabel) yang bisa digunakan berkali-kali dan mereferensi ke dirinya secara rekursif.

And here is the result….

N Level Hierarchy

N Level Hierarchy

Hierarki tree yang rekursif 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s