Recursive query

Question

Consider this scenario,
Item Hierarchy
ID HierarchyId HierarchyID Name ParentHierarchyId
1 103 100 H1 null
2 104 101 H2 100
102 H3 101
103 H4 102
104 H5 null
HierarchyId in Item table is the foreign key to HierarchyId in Hierarchy Table.
ParentHierarchyId is self referenced foreign key.

I need to write a query that will join Item with Hierarchy table and return the foll resultset,

1 H1/H2/H3/H4
2

---

Anonymous

Answer 1

Hopefully you are looking for this...

follow below example...

declare @item table (ID int, HierarchyId int) 


Declare @Hierarchy table (HierarchyID int, Name nvarchar(10), ParentHierarchyId int) 


insert into @item 
select 1 ,103  union all 
select 2, 104 


insert into @Hierarchy
select 101 ,'H2' ,100 union all 
select 100 ,'H1',  null union all 
select 102,  'H3', 101 union all
select 103 ,'H4' , 102 union all 
select 104 , 'H5', null 

select * from @item 
select * from @Hierarchy

declare @CTE_Hirarchy table  (id int,HierarchyID int,name nvarchar(10),ParentHierarchyId int)

;with CTE_hierarchy  as
( select i.ID, h.HierarchyID, Name,ParentHierarchyId  from @Hierarchy h join @item i on h.HierarchyID=i.HierarchyId
union all 
select c.id , h.HierarchyID, h.Name,h.ParentHierarchyId  from @Hierarchy h join CTE_hierarchy c 
on c.ParentHierarchyId=h.HierarchyID
)

insert into @CTE_Hirarchy 
select * from CTE_hierarchy


select id, isnull(substring((select '/'+ name from @CTE_Hirarchy where id=c.id and ParentHierarchyId is not null  for XML path('') ) ,2,9999),'') as STR
from @CTE_Hirarchy c group by id 

Thanks,

saurabh

---

http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html

Answer 2

Is the expected result correct?

If Item = 2 is tied to HierarchyID = 104, then why the path for this item is empty in the result?

DECLARE @Item TABLE (
ItemID int NOT NULL PRIMARY KEY,
[HierarchyID] int NOT NULL
);

DECLARE @Hierarchy TABLE (
[HierarchyID] int NOT NULL PRIMARY KEY, 
Name varchar(10) NOT NULL,
ParentHierarchyId int NULL
);

INSERT INTO @Item (ItemID, [HierarchyID])
VALUES
	(1, 103),
	(2, 104)

INSERT INTO @Hierarchy ([HierarchyID], Name, ParentHierarchyId)
VALUES
	(100, 'H1', NULL),
	(101, 'H2', 100),
	(102, 'H3', 101),
	(103, 'H4', 102),
	(104, 'H5', NULL);

WITH R AS (
SELECT
	I.ItemID,
	H.ParentHierarchyId,
	CAST(H.Name AS varchar(MAX)) AS HierarchyPath,
	1 AS rn
FROM
	@Item AS I
	INNER JOIN
	@Hierarchy AS H
	ON I.[HierarchyID] = H.[HierarchyID]

UNION ALL

SELECT
	C.ItemID,
	P.ParentHierarchyId,
	CAST(P.Name + '/' + C.HierarchyPath AS varchar(MAX)),
	C.rn + 1
FROM
	R AS C
	INNER JOIN
	@Hierarchy AS P
	ON P.[HierarchyID] = C.ParentHierarchyId
)
SELECT TOP (1) WITH TIES
	ItemID,
	HierarchyPath
FROM
	R
ORDER BY
	ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY rn DESC);
GO

/*

ItemID	HierarchyPath
1	H1/H2/H3/H4
2	H5

*/

---

AMB

Some guidelines for posting questions...