Select Parent and Child with HiearchyID

Question

Post reply

Select Parent and Child with HiearchyID

udaaf

SSC Veteran

Points: 208
More actions
September 4, 2014 at 8:10 pm

#290487

All,
How to select parent and child with HierarchyID base on child attribute.
Here's my table :
CREATE TABLE #BOMTbl
(
ItemNo HIERARCHYID NOT NULL,
Lvl AS ItemNo.GetLevel() PERSISTED,
MatID VARCHAR(25) NULL,
CONSTRAINT PK_#BOMTbl PRIMARY KEY NONCLUSTERED(ItemNo)
);
insert into #BOMTbl (ItemNo,MatID) values
('/1/',''),
('/1/1/',''),
('/1/1/1/','MA-A'),
('/1/1/2/','MA-A'),
('/1/1/3/','MA-B'),
('/1/2/',''),
('/1/2/1/','MA-B'),
('/1/2/2/','MA-B'),
('/1/3/',''),
('/1/3/1/','PW-A'),
('/1/3/2/','PW-A'),
('/1/4/',''),
('/1/4/1/','PW-B'),
('/1/4/2/','PW-B'),
('/1/5/','0001'),
('/1/6/','0001'),
('/1/7/','0002'),
('/1/8/','0003')
I wanna the result like this :
ItemNoItemIDLvlMatID
0x5AC0/1/1/2
0x5AD6/1/1/1/3MA-A
0x5ADA/1/1/2/3MA-A
0x5B40/1/2/2
0x5ADE/1/1/3/3MA-B
0x5B56/1/2/1/3MA-B
0x5B5A/1/2/2/3MA-B
Thanks,
udaaf

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Here is a quick sample, should get you passed the hurdle.

😎

USE tempdb;

GO

CREATE TABLE #BOMTbl

(

ItemNo HIERARCHYID NOT NULL,

Lvl AS ItemNo.GetLevel() PERSISTED,

MatID VARCHAR(25) NULL,

CONSTRAINT PK_#BOMTbl PRIMARY KEY NONCLUSTERED(ItemNo)

);

insert into #BOMTbl (ItemNo,MatID) values

('/1/',''),

('/1/1/',''),

('/1/1/1/','MA-A'),

('/1/1/2/','MA-A'),

('/1/1/3/','MA-B'),

('/1/2/',''),

('/1/2/1/','MA-B'),

('/1/2/2/','MA-B'),

('/1/3/',''),

('/1/3/1/','PW-A'),

('/1/3/2/','PW-A'),

('/1/4/',''),

('/1/4/1/','PW-B'),

('/1/4/2/','PW-B'),

('/1/5/','0001'),

('/1/6/','0001'),

('/1/7/','0002'),

('/1/8/','0003');

;WITH HIERARCHY_BASE AS

(

SELECT

ROW_NUMBER() OVER

(

PARTITION BY B.ItemNo.GetAncestor(1)

ORDER BY (SELECT NULL)

) AS ITEM_RID

,B.ItemNo

,B.Lvl

,B.MatID

,B.ItemNo.GetAncestor(1) AS Parent

FROM #BOMTbl B

WHERE B.MatID LIKE 'MA-%'

)

,PARENTS_CHILDREN AS

(

SELECT

HB.Parent AS ItemNo

,HB.Parent.ToString() AS ItemID

,HB.Lvl - 1 AS Lvl

,'' AS MatID

FROM HIERARCHY_BASE HB

WHERE HB.ITEM_RID = 1

UNION ALL

SELECT

HB.ItemNo

,HB.ItemNo.ToString()

,HB.Lvl

,HB.MatID

FROM HIERARCHY_BASE HB

)

SELECT

PC.ItemNo

,PC.ItemID

,PC.Lvl

,PC.MatID

FROM PARENTS_CHILDREN PC

ORDER BY PC.ItemID;

Results

ItemNo ItemID Lvl MatID

------- -------- ---- ------

0x5AC0 /1/1/ 2

0x5AD6 /1/1/1/ 3 MA-A

0x5ADA /1/1/2/ 3 MA-A

0x5ADE /1/1/3/ 3 MA-B

0x5B40 /1/2/ 2

0x5B56 /1/2/1/ 3 MA-B

0x5B5A /1/2/2/ 3 MA-B

udaaf SSC Veteran Points: 208 More actions · Answer 2

@Eirikur Eiriksson,

Thanks for your respond. It's work :-).

Could you tell me the great article or book for learn HierarchyID. Because I found some query for solved my problem. I wanna learn the basic logic about HierarchyID.

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 3

udaaf (9/8/2014)
@Eirikur Eiriksson,
Thanks for your respond. It's work :-).
Could you tell me the great article or book for learn HierarchyID. Because I found some query for solved my problem. I wanna learn the basic logic about HierarchyID.

I don't know if you'd consider it to be great but a Google search quickly turned up the following...

http://msdn.microsoft.com/en-us/library/bb677193.aspx

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

udaaf SSC Veteran Points: 208 More actions · Answer 4

Jeff Moden (9/23/2014)
udaaf (9/8/2014)
@Eirikur Eiriksson,
Thanks for your respond. It's work :-).
Could you tell me the great article or book for learn HierarchyID. Because I found some query for solved my problem. I wanna learn the basic logic about HierarchyID.
I don't know if you'd consider it to be great but a Google search quickly turned up the following...
http://msdn.microsoft.com/en-us/library/bb677193.aspx

@jeff,

Thanks for your info