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