CODEWITHSUNDEEP
sql-server
Bhavik Vora
Bhavik Vora

Reputation: 11

sys_connect_by_path in MSSQL

Please help me to convert the below query in MSSQL. 

select sys_connect_by_path(title, '/') "Path", ITEM_ID, PARENT_ID
from lib_items 
where item_type = '1'start with title = 'root'
connect by prior item_id = parent_id

Upvotes: 1

Views: 4945

Answers (1)

Ebis
Ebis

Reputation: 380

IN SQL-Server using a recursive common table expression like this:

; WITH lib_items(title, item_id, parent_id)
       AS   (  SELECT title, item_id, parent_id
               FROM     (VALUES     ('root'  , 1, NULL)
                                ,   ('child1', 2, 1)
                                ,   ('child2', 3, 2)
                                ,   ('child3', 4, 1)
                                )   AS lib_items (title, item_id, parent_id)

           )
   ,  CTE AS
           (   SELECT   CAST (title AS VARCHAR(8000)) AS title, item_id, parent_id
               FROM     lib_items   
               WHERE    parent_id IS NULL
               UNION ALL
               SELECT   CAST(P.Title + '\' +C.Title AS VARCHAR(8000)), C.item_id, C.parent_id
               FROM     lib_items AS c 
               JOIN     CTE AS p ON c.parent_id = p.item_id
           )

SELECT   title, item_id, parent_id
FROM     CTE

Result:

title              |item_id|parent_id
-------------------|----------------
root               | 1     | NULL
root\child1        | 2     | 1
root\child3        | 4     | 1
root\child1\child2 | 3     | 2

Upvotes: 1

Related Questions