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
4
-
i am not sure about your result..would you please explain ?Rajib Ghosh– Rajib Ghosh2014-11-23 11:02:00 +00:00Commented Nov 23, 2014 at 11:02
-
I think there is no GROUP_CONCAT function in MSSQL.Rajib Ghosh– Rajib Ghosh2014-11-23 11:04:55 +00:00Commented Nov 23, 2014 at 11:04
-
I want to have a parent child relation in a row that sorted base on relation .MAYAHOO– MAYAHOO2014-11-23 11:06:23 +00:00Commented Nov 23, 2014 at 11:06
-
plz check this link,i think this is useful for you linkRajib Ghosh– Rajib Ghosh2014-11-23 11:12:35 +00:00Commented Nov 23, 2014 at 11:12
Add a comment
|
2 Answers
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.
Comments
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
1 Comment
Amol Kadam
thnks @Konstantin for your suggestion, i had answered this as per original request which later on got changed.