CODEWITHSUNDEEP
sqlsql-server
MAYAHOO
MAYAHOO

Reputation: 3

How to create path with sql query

What is the best solution : I have a table in sqlserver with these contents .

PARENT CHILD  Level 
A        B      0
B        C      1
C        D      2
D        E      3

I need a query to create this result : A/B/C/D/E

Upvotes: 0

Views: 1248

Answers (2)

Amol Kadam
Amol Kadam

Reputation: 85

Use Coalesce to combine rows into single column separated by '/' delimiter

DECLARE @path VARCHAR(8000) = (SELECT parent FROM test WHERE LEVEL = 0)

SELECT @path = COALESCE(rtrim(@path) + '/', '') + child FROM test

SELECT @path

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can use a recursive CTE for this:

with cte as (
       select t.parent as p, t.parent as c, 0 as lev
       from table t
       where not exists (select 1 from t t2 where t2.child = t.parent)
       union all
       select cte.p, t.child, lev + 1
       from cte join
            table t
            on cte.c = t.parent
     )
select stuff((select '/' + cte2.c
              from cte cte2
              where cte2.p = cte.p
              order by cte2.lev
              for xml path ('')
             ), 1, 1, '') as path
from cte
group by cte.p;

Here is a SQL Fiddle.

Upvotes: 1

Related Questions