Dari Aknutman

Selamat Datang semoga layanan kami memberikan informasi yang anda cari. (Dan mohon comment-nya :-D)

Sunday, February 12, 2017

SQL Server Recursive Query Sample

Dear Pembaca Setia,
Kali ini penulis ingin menge-share contoh SQL Server query untuk logika recursive. Langsung saja, kita lihat contoh berikut;

DECLARE @tmp TABLE
(
    id int, parent_id int, val_name varchar(50)
)

INSERT INTO @tmp
(
  id, parent_id, val_name
)
(
    SELECT 1, 1, 'Super Admin'
UNION ALL SELECT 2, 1, 'Admin 1'
UNION ALL SELECT 3, 1, 'Admin 2'
UNION ALL SELECT 4, 2, 'User 1'
UNION ALL SELECT 5, 1, 'Admin 3'
)

;WITH cte AS
(
    SELECT 
id, parent_id, val_name
FROM 
@tmp a
WHERE
a.id = a.parent_id
    UNION ALL
SELECT
        b.id, b.parent_id, b.val_name
    FROM
        @tmp b
    INNER JOIN
        cte c
ON b.parent_id = c.id
    WHERE
        b.id <> b.parent_id
)

SELECT *
FROM cte

DELETE @tmp

--===========================================================
-- Dan berikut Hasilnya
--===========================================================

Demikian tutorial hari ini, terima kasih sudah berkenan mampir.

Salam,
Aknutman